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

GO

 

IF EXISTS (SELECT *

           FROM sysobjects

           WHERE name = N'PhoneList')

           DROP FUNCTION PhoneList

GO

 

CREATE FUNCTION PhoneList(@Dept_Code numeric(5))

RETURNS @Result TABLE

(Emp_Code numeric(5),

  Emp_Name varchar(50),

  Phone varchar(50)

)

AS

BEGIN

   INSERT INTO @Result (Emp_Code, Emp_Name, Phone)

   (SELECT Emp_Code, Emp_Name, Phone FROM EMP

   WHERE Dept_Code = @Dept_Code)

   RETURN

END

GO

 

-- 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



* * * * *


Email Your Comment To AUTHOR