March 24, 2019
Hot Topics:

User-Defined Functions: Powerful Alternatives to Views and Stored Procedures

  • April 14, 2006
  • By Jeffrey Juday
  • Send Email »
  • More Articles »

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
   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.

Page 1 of 2

Comment and Contribute


(Maximum characters: 1200). You have characters left.



Enterprise Development Update

Don't miss an article. Subscribe to our newsletter below.

Thanks for your registration, follow us on our social networks to keep up-to-date