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