What is function in sql server and uses of it?

Generally functions are used to reduce redundancy of queries. I have two tables QuestionDetails andUserDetails 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 getcreatedby 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) fromQuestionDetails

http://pramadha.com/

Advertisements

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s