Table-Valued User-Defined Functions
User-defined functions that return a table data type can be powerful alternatives to views. These functions are referred to as table-valued functions. A table-valued user-defined function can be used where table or view expressions are allowed in Transact-SQL queries. While views are limited to a single SELECT statement, user-defined functions can contain additional statements that allow more powerful logic than is possible in views.
A table-valued user-defined function can also replace stored procedures that return a single result set. The table returned by a user-defined function can be referenced in the FROM clause of a Transact-SQL statement, but stored procedures that return result sets cannot.
Components of a Table-Valued User-defined Function
In a table-valued user-defined function:
The RETURNS clause defines a local return variable name for the table returned by the function. The RETURNS clause also defines the format of the table. The scope of the local return variable name is local within the function.
The Transact-SQL statements in the function body build and insert rows into the return variable defined by the RETURNS clause.
When a RETURN statement is executed, the rows inserted into the variable are returned as the tabular output of the function. The RETURN statement cannot have an argument.
No Transact-SQL statements in a table-valued function can return a result set directly to a user. The only information the function can return to the user is the table returned by the function.
Note
The text in row table option is automatically set to 256 for a table returned by a user-defined function. This cannot be changed. The READTEXT, WRITETEXT, and UPDATETEXT statements cannot be used to read or write parts of any text, ntext, or image columns in the table. For more information, see In-Row Data.
Example
The following example creates the dbo.ufnGetContactInformation function and demonstrates the components of the table-valued function. In this function, the local return variable name is @retContactInformation. Statements in the function body insert rows into this variable to build the table result returned by the function.
USE AdventureWorks2008R2;
GO
IF OBJECT_ID(N'dbo.ufnGetContactInformation', N'TF') IS NOT NULL
DROP FUNCTION dbo.ufnGetContactInformation;
GO
CREATE FUNCTION dbo.ufnGetContactInformation(@ContactID int)
RETURNS @retContactInformation TABLE
(
-- Columns returned by the function
ContactID int PRIMARY KEY NOT NULL,
FirstName nvarchar(50) NULL,
LastName nvarchar(50) NULL,
JobTitle nvarchar(50) NULL,
ContactType nvarchar(50) NULL
)
AS
-- Returns the first name, last name, job title, and contact type for the specified contact.
BEGIN
DECLARE
@FirstName nvarchar(50),
@LastName nvarchar(50),
@JobTitle nvarchar(50),
@ContactType nvarchar(50);
-- Get common contact information
SELECT
@ContactID = BusinessEntityID,
@FirstName = FirstName,
@LastName = LastName
FROM Person.Person
WHERE BusinessEntityID = @ContactID;
-- Get contact job title
SELECT @JobTitle =
CASE
-- Check for employee
WHEN EXISTS(SELECT * FROM Person.Person AS p
WHERE p.BusinessEntityID = @ContactID AND p.PersonType = 'EM')
THEN (SELECT JobTitle
FROM HumanResources.Employee AS e
WHERE e.BusinessEntityID = @ContactID)
-- Check for vendor
WHEN EXISTS(SELECT * FROM Person.Person AS p
WHERE p.BusinessEntityID = @ContactID AND p.PersonType = 'VC')
THEN (SELECT ct.Name
FROM Person.ContactType AS ct
INNER JOIN Person.BusinessEntityContact AS bec
ON bec.ContactTypeID = ct.ContactTypeID
WHERE bec.PersonID = @ContactID)
-- Check for store
WHEN EXISTS(SELECT * FROM Person.Person AS p
WHERE p.BusinessEntityID = @ContactID AND p.PersonType = 'SC')
THEN (SELECT ct.Name
FROM Person.ContactType AS ct
INNER JOIN Person.BusinessEntityContact AS bec
ON bec.ContactTypeID = ct.ContactTypeID
WHERE bec.PersonID = @ContactID)
ELSE NULL
END;
-- Get contact type
SET @ContactType =
CASE
-- Check for employee
WHEN EXISTS(SELECT * FROM Person.Person AS p
WHERE p.BusinessEntityID = @ContactID AND p.PersonType = 'EM')
THEN 'Employee'
-- Check for vendor
WHEN EXISTS(SELECT * FROM Person.Person AS p
WHERE p.BusinessEntityID = @ContactID AND p.PersonType = 'VC')
THEN 'Vendor Contact'
-- Check for store
WHEN EXISTS(SELECT * FROM Person.Person AS p
WHERE p.BusinessEntityID = @ContactID AND p.PersonType = 'SC')
THEN 'Store Contact'
-- Check for individual consumer
WHEN EXISTS(SELECT * FROM Person.Person AS p
WHERE p.BusinessEntityID = @ContactID AND p.PersonType = 'IN')
THEN 'Consumer'
-- Check for general contact
WHEN EXISTS(SELECT * FROM Person.Person AS p
WHERE p.BusinessEntityID = @ContactID AND p.PersonType = 'GC')
THEN 'General Contact'
END;
-- Return the information to the caller
IF @ContactID IS NOT NULL
BEGIN
INSERT @retContactInformation
SELECT @ContactID, @FirstName, @LastName, @JobTitle, @ContactType;
END;
RETURN;
END;
GO
The following example uses the table-valued function dbo.ufnGetContactInformation in the FROM clause of two SELECT statements.
USE AdventureWorks2008R2;
GO
SELECT ContactID, FirstName, LastName, JobTitle, ContactType
FROM dbo.ufnGetContactInformation(1209);
GO
SELECT ContactID, FirstName, LastName, JobTitle, ContactType
FROM dbo.ufnGetContactInformation(5);
GO
See Also