How to write Table Valued User Defined Function in SQL Server 2005

User Defined Function in SQL Server 2005 can be of two types depending on return type; Scalar and Table Valued User Defined Function. Here, I am going to explain how to write the later one.


This udf_CollectExperinceInformation function takes ApplicantID as parameter and returns experience details with Ranking against any specific ApplicantID. Table t_ExperienceType has two fields- ExperienceType_Id and ExperienceType_Name. t_ApplicantExperience table has fields; applicantExperience_ExperienceId, applicantExperience_YearOfExperience, applicantExperience_ApplicantId. This User Defined Function adds ExperienceRanking depending on year of experience.


CREATE FUNCTION udf_CollectExperinceInformation(@ApplicantID int)
RETURNS @ExperienceDetails TABLE
(ExperienceArea varchar(50),YearOfExperience int,ExperienceRanking varchar(2))
As
BEGIN

INSERT @ExperienceDetails(ExperienceArea,YearOfExperience)
SELECT a.ExperienceType_Name, b.applicantExperience_YearOfExperience
FROM t_ExperienceType a,t_ApplicantExperience b
WHERE b.applicantExperience_ExperienceId = a.ExperienceType_Id
AND b.applicantExperience_ApplicantId = @ApplicantID

UPDATE @ExperienceDetails
SET ExperienceRanking = ‘A’
WHERE YearOfExperience > 2

UPDATE @ExperienceDetails
SET ExperienceRanking = ‘B’
WHERE YearOfExperience <= 2
RETURN

END


BINGO!! see how easily you can collect all experience related information using this User Defined Function.


SELECT * FROM dbo.udf_CollectExperinceInformation(1167)


Here is the output.



Thanks
A Rahim Khan

Advertisements
  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

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

%d bloggers like this: