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

Microsoft SQL Server Blog. Fight the fear of SQL with SQLHub.com. Founder is Ritesh Shah

Author: Riteshshah

Ritesh Shah is a database professional with having 16+ years of experience in various domain of IT field. He is a author of many technical articles on Microsoft Technology and authored a book of SQL Server 2012 Performance Tuning cookbook. He loves work with different Microsoft Products.