Table-Valued User Defined Function in SQL SERVER
We will look into the Table-Valued User Defined Function
The syntax for Table-Valued is as follows
CREATE FUNCTION [ schema_name. ] function_name ( [ { @parameter_name [ AS ] [ type_schema_name. ] parameter_data_type [ = default ] [READONLY] } [ ,...n ] ] )
RETURNS @return_variable TABLE
[ WITH [ ,...n ] ]
[ AS ]
BEGIN
function_body
RETURN
END [ ; ]
The below example shows how to create a multistatement table-valued function which provides details based on the department status.
CREATE FUNCTION DEPT_DETAILS_BY_STATUS(@Status varchar(10))
RETURNS @DEPTSTATUS TABLE
(
DEPTID int,
DEPTNAME varchar(100),
STATUS varchar(10)
)
BEGIN
INSERT INTO @DEPTSTATUS Select * from Dept where Status = @Status
RETURN
END
The usage of the same is as follows
Select EmployeeID, FirstName, LastName, Dt.DeptName from Employee
INNER JOIN DEPT_DETAILS_BY_STATUS('Active') Dt ON Dt.DeptID = Employee.DeptID
The syntax for Table-Valued is as follows
CREATE FUNCTION [ schema_name. ] function_name ( [ { @parameter_name [ AS ] [ type_schema_name. ] parameter_data_type [ = default ] [READONLY] } [ ,...n ] ] )
RETURNS @return_variable TABLE
[ WITH
[ AS ]
BEGIN
function_body
RETURN
END [ ; ]
The below example shows how to create a multistatement table-valued function which provides details based on the department status.
CREATE FUNCTION DEPT_DETAILS_BY_STATUS(@Status varchar(10))
RETURNS @DEPTSTATUS TABLE
(
DEPTID int,
DEPTNAME varchar(100),
STATUS varchar(10)
)
BEGIN
INSERT INTO @DEPTSTATUS Select * from Dept where Status = @Status
RETURN
END
The usage of the same is as follows
Select EmployeeID, FirstName, LastName, Dt.DeptName from Employee
INNER JOIN DEPT_DETAILS_BY_STATUS('Active') Dt ON Dt.DeptID = Employee.DeptID
Comments