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

IF EXISTS (SELECT *

FROM sysobjects

WHERE name = N'EmployeeList')

DROP FUNCTION EmployeeList

CREATE FUNCTION EmployeeList(@Dept_Code numeric(5))

RETURNS TABLE

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)

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

* * * * *

Your Comment To AUTHOR