Reuse SQL Code Easily With UDFs
Use one of SQL Server 2000's newer features to write efficient and reusable SQL code.
by Roman Rehak
September 2003 Issue
Technology Toolbox: SQL Server 2000
One of the more useful newer features SQL Server 2000 offers is the ability to create user-defined functions (UDFs). A SQL Server UDF encapsulates a piece of SQL code and makes it easy to reuse in other database objects. I'll provide you with guidelines and recommendations for using each type of UDF and discuss scenarios where UDFs provide a better alternative to views and stored procedures. I'll also show you how to implement and manage your own system user-defined functions.
UDFs come in two flavors: scalar and table-valued. Scalar functions return a single value, and table-valued functions return a table. You can use two types of table-valued functions: inline functions and multistatement functions. Inline table-valued functions return the result of a single SELECT statement. Multistatement table-valued functions have a function body that contains SQL code for building the return table. Both types of UDFs can simplify your SQL code, because they can embed calculations, transformations, or joins you use frequently and provide a component-like object that other database objects can use easily.
I'll start by discussing scalar functions. You can use these functions anywhere in SQL code where a scalar value is expected. You use them most commonly in SELECT statements or in the WHERE clause, but you can also use them in CASE statements, computed columns, or in RETURN statements in stored procedures. Scalar UDFs can be extremely useful when you use them in check constraints and defaults. They can contain complex logic to generate a value for a default or a check constraint, but they're much easier to write than triggers.
Once you use a function in a constraint, you can't modify or drop the function until you drop or modify the constraint. This restriction makes the use of UDFs in constraints harder to manage if you need to modify the value the function returns. You can work around this restriction sometimes by creating a data-driven UDF. For example, suppose you have a table with a location column. You've defined a column default, but the default location changes periodically, so you must modify the column default every time you need to change the default location. If you use a scalar function instead and retrieve the location value from a helper table, you need to modify only the value in the helper table. The table starts using the new value without any modifications to the column default. This implementation simplifies administration, because it helps you avoid modifying column constraints. It's especially useful if multiple tables use the same default.
Use Scalar Functions in Complex Reports
When you use scalar UDFs in SELECT statements, they usually take one or more parameters; perform some calculations, lookups, or transformations; and include the result in the resultset. Scalar functions can be quite handy in complex, report-type queries where each row must include results you collect from related tables. One common requirement is to produce a comma-delimited list of related items and include it as a column in the resultset. A typical solution is to create a temporary table with a blank column, populate it with data, then create a cursor against the table and use a nested cursor to build a comma-delimited list of child items. You can achieve the same result much more easily by creating a scalar function for building the list (see Listing 1). The GetAuthorList function in Listing 1 accepts TitleID as an input parameter, builds a list of the book authors, and returns the list. This query shows how you can use the function to include the author list in the resultset (see Figure 1):
USE pubs
GO
SELECT title_ID as TitleID, Title,
dbo.GetAuthorList(title_ID) AS Authors
FROM Titles
Back to top
|