Programming in MS SQL Server 2012

Multi Table-valued Function

In-line table functions returns a single table variable that was created by a SELECT statement. Multi-statement table functions returns a table variable whose structure was created by code, similar to a CREATE TABLE statement. It is useful when complex data manipulation inside the function is required.
The following example describes a Multi Table-valued function concept.

-- Example 97 --

USE EBusiness

IF EXISTS (SELECT *

FROM sysobjects

WHERE name = N'PhoneList')

DROP FUNCTION PhoneList

CREATE FUNCTION PhoneList(@Dept_Code numeric(5))

RETURNS @Result TABLE

(Emp_Code numeric(5),

Emp_Name varchar(50),

Phone varchar(50)

)

BEGIN

INSERT INTO @Result (Emp_Code, Emp_Name, Phone)

(SELECT Emp_Code, Emp_Name, Phone FROM EMP

WHERE Dept_Code = @Dept_Code)

RETURN

END

-- Execute Function with Argument Data Values --

SELECT * FROM PhoneList('10')

SELECT * FROM PhoneList('20')

SELECT * FROM PhoneList('30')

SELECT * FROM PhoneList('40')

SELECT * FROM PhoneList('50')

Query Output Screen

* * * * *

Your Comment To AUTHOR