User-Defined Functions

I first started working with SQL Server with version 6.0. Although I had first worked with SQL in a VMS environment in 1989, I had done most of my database work in a flat-file environment. I was surprised that I didn't miss the cursor-like aspects of row operations as much as the ability to easily apply functions. With SQL Server 2000, I can finally create user-defined functions (UDFs) to use not only in scripts, but also inline with Data Manipulation Language (DML) statements. SQL Server 2000 has two types of UDFs: those that return a scalar value, and those that return tables. In this article, I discuss examples of each type and briefly describe how you might use UDFs in SQL Server 2000 applications.

UDFs That Return a Scalar Value
UDFs that return a scalar value are probably what you most expect. Like GETDATE(), USER(), and many of SQL Server's own built-in functions, this type of UDF lets you define code that returns a scalar value to the calling script, procedure, or line of code.

In many ways, UDFs look like stored procedures. Like stored procedures, UDFs accept parameters and return a value. Unlike stored procedures, however, they can't contain output parameters. More importantly, a UDF return value isn't limited to an integer. Instead, the value can be any valid SQL Server data type, except for BLOBs, cursors, and timestamps.

Let's look at an example that shows how easily you can build a function to deal with a problem that SQL Server developers commonly face—how to see whether a datetime field entry occurred on a specific day. Your datetime field usually has specific time-of-day information that makes it difficult to compare just entry dates. More specifically, two fields with the same date but different times won't return a match.

A typical solution is to convert the date to a string, then back to a date to truncate the time information. If you perform this conversion on both of the datetime fields you want to compare, the comparison based on just the date (without the time) will succeed. The solution might look like the code in Listing 1, page 62. Following are the results:

(1 row(s) affected)

KeyCol		TestDate
-------		-----------
(0 row(s) affected)

KeyCol		TestDate
-------		-----------
12000-02-10 21:29:43.140

(1 row(s) affected)

The second query became a little messy—the more functions you embed in your query, the more convoluted the code becomes. Sometimes, when I've had to strip down many dates to just the day, not the time, the resulting code has become difficult to read.

Now I do the same thing with a simple UDF. First, I use the new CREATE FUNCTION command to create the function; I format this command much like a stored procedure. I could code the query this way:

CREATE FUNCTION DayOnly(@Date datetime)
RETURNS varchar(12)
AS
BEGIN
RETURN CONVERT(varchar(12), @Date, 101)
END

Now I reformat the query slightly to take advantage of the new function:

SELECT * 
FROM TestTable
WHERE dbo.DayOnly(TestDate) = dbo.DayOnly(GETDATE())

Even for this simple query, the new code is much more readable. The call works much as it would from most languages that support functions, with one hitch: The owner (schema) is required to execute our function. For some reason, SQL Server doesn't resolve functions as it does other objects.

UDFs have more advantages than just readability. You can embed queries in UDFs and use them to encapsulate subqueries. In fact, you can encapsulate almost any query that returns a discrete value. To demonstrate, let's encapsulate the stock subquery example from SQL Server Books Online (BOL). The subquery version looks like Listing 2. An encapsulated version of the average subqueries and the difference subqueries appears in the functions in Listing 3. Notice that it's legal to embed one UDF in another one.