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