Multi Statement Table Valued Function in SQL Server 2005:

Multi Statement Table Valued User Define Function is very useful and handy in-order to retain complex code with the table variable. This function populates table variable inside and then returns record set like stored procedure.

–creating demo table

CREATE TABLE emps

(

Name VARCHAR(50),

Dept VARCHAR(10),

Company VARCHAR(15)

)

–INSERT records

INSERT INTO emps

SELECT ‘Ritesh’,‘MIS’,‘echem’ UNION ALL

SELECT ‘Bihag’, ‘MIS’, ‘CT’ UNION ALL

SELECT ‘Rajan’, ‘account’,‘Marwadi’ UNION ALL

SELECT ‘Alka’,‘account’,‘tata’ UNION ALL

SELECT ‘Alpesh’,‘Chemical’,‘echem’

GO

–creating multi-statment inline table valued function

CREATE FUNCTION dbo.GetEmployeeData1(@dept VARCHAR(10))

RETURNS @empData TABLE

(

Name VARCHAR(10),

Dept VARCHAR(10),

Company VARCHAR(10)

)

AS

BEGIN

INSERT @empData(Name,Dept,Company)

SELECT Name,Dept,Company FROM emps WHERE dept=@dept

RETURN

END

GO

–use above function

SELECT * FROM dbo.GetEmployeeData1(‘MIS’)

Reference: Ritesh Shah
http://www.sqlhub.com
Note: Microsoft Books online is a default reference of all articles but examples and explanations prepared by Ritesh Shah, founder of http://www.SQLHub.com

Advertisements

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: