Programming in MS SQL Server 2012 |
Inline Table-valued FunctionUser-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) |
* * * * *