Aspdotnet-Suresh

aspdotnet-suresh offers C#.net articles and tutorials,csharp dot net,asp.net articles and tutorials,VB.NET Articles,Gridview articles,code examples of asp.net 2.0 /3.5,AJAX,SQL Server Articles,examples of .net technologies

Introduction:

Here I will explain
sql server function example or create function example in sql server 2008. Functions in sql server is used to accept input parameters and it will return single value or table.
Description:

Generally functions are used to reduce redundancy of queries. I have two tables QuestionDetails and UserDetails will be like as shown below

QuestionDetails

QuestionId
Subject
Description
CreatedBy
1
Test
simple desc
1
2
Welcome to asp
check forums site
1
3
I have a problem
ssms not working
2

UserDetails

Userid
Username
Location
Designation
1
Sureshdasari
Chennai
SSE
2
Rohinidasari
Chennai
agbsc

Now from QuestionDetails table we need to get question details with createdBy name. To get createdby name we can join these two tables but to improve query performance we need to avoid using joins in sql server. To get username instead of using joins we can create function like as shown below


CREATE FUNCTION fn_getusername
(@userid int)
RETURNS VARCHAR(64)
AS
BEGIN
DECLARE @name VARCHAR(64)
SELECT @name=UserName FROM UserDetails WHERE UserId=@userid
RETURN @name
END
Once we written this function now we need to write the query like as shown below to get question details with username


Select QuestionId, Subject, Description, username=dbo.fn_getusername(CreatedBy) from QuestionDetails
Once we run above query we will get output like as shown below

Output:


If you enjoyed this post, please support the blog below. It's FREE!

Get the latest Asp.net, C#.net, VB.NET, jQuery, Plugins & Code Snippets for FREE by subscribing to our Facebook, Twitter, RSS feed, or by email.

subscribe by rss Subscribe by RSS subscribe by email Subscribe by Email

6 comments :

Anonymous said...

good

Rajkumar Palnati said...

Hi Suresh Good Example.

what is

1.Inline Table - valued Function
2.Multi - Statement Table - valued Function
3.Scalar - valued Function

Satish Bajpai said...

nice one

vah said...

vah

Anonymous said...

good one dude...

Anonymous said...

reducing join operation and increasing query performance very nice ...i don't know dis idea before reading this article ..i m quite impress with this article keep it up dude,

Give your Valuable Comments

Other Related Posts

© 2010-2012 Aspdotnet-Suresh.com. All Rights Reserved.
The content is copyrighted to Suresh Dasari and may not be reproduced on other websites without permission from the owner.