A table-valued function returns a table as an output, which can be derived as a part of a SELECT statement. Table-valued function return the output as a table data type. The table data is a special data type used to store a set of rows, which return the result set of a table-valued function. Table-valued functions are of two types:
Consider an example where the inline table-valued function, fx_Department_GName, accepts a group name as parameter and returns the details of the departments that belong to the group from the Department table. You can create the function by using the following statement:
CREATE FUNCTION fx_Department_GName ( @GrName nvarchar (20) )
RETURNS table
AS
RETURN (
SELECT *
FROM HumanResources.Department
WHERE GroupName=@GrName
)
GO
You can use the following statement to execute the fx_Department_Gname function with a specified argument:
SELECT * FROM fx_Department_GName (‘Manufacturing’)
The preceding statement will return a result set having the group name ‘Manufacturing’.
Consider another example of an inline function that accepts rate a a parameter and returns all the records that have a rate value greater than the parameter value:
CREATE FUNCTION HumanResources.Emp_Pay (@Rate int)
RETURNS table
AS
RETURN (
SELECT e.EmployeeID, e.Title, er.Rate
FROM HumanResources.Employee AS e
JOIN HumanResources.EmployeePayHistory AS er
ON e.EmployeeID=er.EmployeeID WHERE er.Rate<@Rate
)
GO
The preceding function will return a result set that displays all the records of the employees who have the pay rate greater that the parameter.
Consider an example where the Multistatement table-valued function, PayRate, is created to return a set of records from the EmployeePayHistory table by using the following statements:
CREATE FUNCTION PayRate (@rate money)
RETURNS @table TABLE
(EmployeeID int NOT NULL,
RateChangeDate datetime NOT NULL,
Rate money NOT NULL,
PayFrequency tinyint NOT NULL,
modifiedDate datatime NOT NULL)
AS
BEGIN
INSERT @table
SELECT *
FROM HumanResources.EmployeePayHistory
WHERE Rate > @rate
RETURN
END
The function returns a result set in from of a temporary table, @table, created within the function. You can execute the function by using the following statement:
SELECT * FROM PayRate (45)
Inline Table-Valued Function
An inline table-valued function returns a variable of a table data type from the result set of a single SELECT statement. An inline function does not contain a function body within the BEGIN and END statements.Consider an example where the inline table-valued function, fx_Department_GName, accepts a group name as parameter and returns the details of the departments that belong to the group from the Department table. You can create the function by using the following statement:
CREATE FUNCTION fx_Department_GName ( @GrName nvarchar (20) )
RETURNS table
AS
RETURN (
SELECT *
FROM HumanResources.Department
WHERE GroupName=@GrName
)
GO
You can use the following statement to execute the fx_Department_Gname function with a specified argument:
SELECT * FROM fx_Department_GName (‘Manufacturing’)
The preceding statement will return a result set having the group name ‘Manufacturing’.
Consider another example of an inline function that accepts rate a a parameter and returns all the records that have a rate value greater than the parameter value:
CREATE FUNCTION HumanResources.Emp_Pay (@Rate int)
RETURNS table
AS
RETURN (
SELECT e.EmployeeID, e.Title, er.Rate
FROM HumanResources.Employee AS e
JOIN HumanResources.EmployeePayHistory AS er
ON e.EmployeeID=er.EmployeeID WHERE er.Rate<@Rate
)
GO
The preceding function will return a result set that displays all the records of the employees who have the pay rate greater that the parameter.
Multistatement Table-Valued Function
A Multistatement table-valued function uses multiple statements to build the table that is returned to the calling statement. The function body contains a BEGIN…END block, which holds a series of T-SQL statements to build and insert rows into a temporary table. The temporary table is returned in the result set and is created based on the specification mentioned in the function.Consider an example where the Multistatement table-valued function, PayRate, is created to return a set of records from the EmployeePayHistory table by using the following statements:
CREATE FUNCTION PayRate (@rate money)
RETURNS @table TABLE
(EmployeeID int NOT NULL,
RateChangeDate datetime NOT NULL,
Rate money NOT NULL,
PayFrequency tinyint NOT NULL,
modifiedDate datatime NOT NULL)
AS
BEGIN
INSERT @table
SELECT *
FROM HumanResources.EmployeePayHistory
WHERE Rate > @rate
RETURN
END
The function returns a result set in from of a temporary table, @table, created within the function. You can execute the function by using the following statement:
SELECT * FROM PayRate (45)
Depending on the result set returned by a function can be categorized as deterministic or nondeterministic. Deterministic functions always return the same result whenever they are called with a specific set of input values. However, nondeterministic function may return different results each time they are called with a specific set of input values.An example of a deterministic function is date add, which returns the same result for any given set of argument values for its three parameters. Get date is a nondeterministic function because it is always invoked without any argument, but the return value changes on every execution.