Function Example in SQL Server 2008

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:

In previous articles I explained difference between function and stored procedure in sql server, can function return multiple values in sql server, Substring function Example, SQL Query to remove first and last character from string, DateAdd function Example, Pass table as parameter to procedure and many articles relating to SQL server. Now I will explain how to use function in sql server with example.

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 Email