Creating Table-Valued Functions in SQL

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:

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) )
FROM HumanResources.Department
WHERE GroupName=@GrName

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)
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
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)
(EmployeeID int NOT NULL,
RateChangeDate datetime NOT NULL,
Rate money NOT NULL,
PayFrequency tinyint NOT NULL,
modifiedDate datatime NOT NULL)
INSERT @table
FROM HumanResources.EmployeePayHistory
WHERE Rate > @rate

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.

Creating Table-Valued Functions in SQL Creating Table-Valued Functions in SQL Reviewed by Rhett Butler on November 05, 2014 Rating: 5

No comments