User-Defined Functions: Powerful Alternatives to Views and Stored Procedures
Views and stored procedures allow you to modularize and decouple your Transact-SQL (T-SQL) code well, but have you ever wanted to get more out of them? Like using a parameter-driven view or a stored procedure in a SELECT statement? Well, SQL Server has an often-overlooked alternative to views and stored procedures that you should consider: table-valued user defined functions (UDFs). Table-valued UDFs have all the important features of views and stored procedures and some additional functionality that views and stored procedures lack.
For example, my development team used table-valued UDFs to add a new layer of filtering to an ancient ASP business-intelligence application. Substituting parameter-driven UDFs for tables in the FROM clause of the select statements, we left the core T-SQL largely unchanged. Even better, we were able to automate most of the changes using "search and replace."
Using modified samples from the AdventureWorks sample database that ships with SQL Server 2005, this article explains how to use table-valued UDFs effectively in your T-SQL code. To see the UDFs stored in the AdventureWorks database, use Management Studio to navigate to the area in the database shown in Figure 1.
Figure 1. Location of UDFs in AdventureWorks Database
Every UDF is written in T-SQL (like a stored procedure or view) and has parameter values (like a stored procedure). You use the CREATE FUNCTION statement to define a UDF.
User-Defined Functions Defined
User-defined functions come in the following two flavors:
- Scalar-valued UDFs can return almost any single data type.
- Table-valued UDFs return a temporary table containing multiple rows.
You can use scalar UDFs inside any DML (INSERT, UPDATE, SELECT, and so forth) or T-SQL statement. Table UDFs have a few more limitations, and you typically use a table-valued UDF in the FROM part of a SELECT statement. This article focuses on the uses of table-valued UDFs.
Introduction to table-valued UDFs
There are two types of table-valued UDFs:
- Inline table-valued functions return a TABLE datatype. They each contain a single T-SQL statement.
- Multi-statement table-valued functions return a defined table. They can contain multiple T-SQL statements.
Both types of table-valued UDF return single result sets. The following is an example of an inline table-valued UDF definition:
CREATE FUNCTION dbo.TestInlineFunctionName ( ) RETURNS TABLE AS RETURN ( SELECT 0 as RetVal,* from [Person].[Contact] )
A multi-statement UDF looks similar to an inline UDF, but with one major difference: It contains table definition statements after the RETURNS directive, as in the following sample code:
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 )
Because a multi-statement UDF can include many SELECT statements (as well as other T-SQL statements), it must explicitly populate the returning table, as in the following sample code:
INSERT @retContactInformation SELECT @ContactID, @FirstName, @LastName, @JobTitle, @ContactType;
A multi-statement table-valued UDF enables you to do things such as modify the contents of the returned table. For example, the following code is perfectly legal in a multi-statement UDF:
UPDATE @retContactInformation SET [JobTitle] = 'None'
Now that you are familiar with table-valued functions, it's time to learn how they can replace views and stored procedures.