Programming in MS SQL Server 2012

Inline Table-valued Function



User-defined table-valued functions return a table data type. For an inline table-valued function, there is no function body; the table is the result set of a single SELECT statement. Inline table-valued function like non-updatable view but user-defined function accepts argument variable data values.

The following example describes an Inline Table-valued function concept.

-- Example 96 --

 

USE EBusiness

GO

 

IF EXISTS (SELECT *

           FROM sysobjects

           WHERE name = N'EmployeeList')

           DROP FUNCTION EmployeeList

GO

 

CREATE FUNCTION EmployeeList(@Dept_Code numeric(5))

RETURNS TABLE

AS

RETURN

(SELECT

   EMP.Emp_Code,

   EMP.Emp_Name,

   EMP.DOJ,

   EMP.Salary,

   EMP.Dept_Code,

   DEPT.Dept_Name FROM EMP

INNER JOIN DEPT

ON EMP.Dept_Code = DEPT.Dept_Code

WHERE EMP.Dept_Code = @Dept_Code)

GO

 

-- Execute Function with Argument Data Values --

 

SELECT * FROM EmployeeList(10)

SELECT * FROM EmployeeList(20)

SELECT * FROM EmployeeList(30)

SELECT * FROM EmployeeList(40)

SELECT * FROM EmployeeList(50)


Query Output Screen



* * * * *


Email Your Comment To AUTHOR