User-Defined Functions

When user-defined functions were introduced in SQL Server 2000, this opened the door to a whole new level of functionality. Until then, nearly all business logic had to be in compound expressions with little opportunity to reuse code. In traditional programming languages, functions typically accept any number of values and then return a scalar (single) value. Functions are typically used to perform calculations, to compare, parse, and manipulate values. This describes one of the capabilities of user-defined functions (UDFs), but they can also be used to return sets of data.

Set-based functions can be parameterized like a stored procedure but are used in a SELECT expression like a view. In some ways this makes UDFs the best of both worlds. Three different categories of user-defined functions exist, two of which return result sets. These categories include the following:

• Scalar functions

• Multi-statement table-valued functions

• Inline table-valued functions

Note

One important thing to keep in mind when designing user-defined functions is that other functions called within the script must be deterministic. In other words, the value returned must be dependent only on the value(s) passed to it, and not based on external resources. For example, a UDF cannot call a nondeterministic GetDate() function. Instead, to deal with this limitation, you would pass the date value into the function as a parameter.

Scalar Functions

A scalar function accepts any number of parameters and returns one value. The term scalar differentiates a single, "flat" value from more complex structured values, such as arrays or result sets. This pattern is much like that of traditional functions written in common programming languages.

The script syntax is quite simple. Input parameters are declared within parentheses followed by the return value declaration. All statements must be enclosed in a BEGIN. . . END block. In this simple example, I calculate the age by getting the number of days between the birth date and today's date. Because my function can't call the nondeterministic GETDATE() function, this value must be passed into the function using the @Today parameter. The number of days is divided by the average number of days in a year to determine the result:

 CREATE FUNCTION fnGetAge (@BirthDate DateTime, @Today DateTime) RETURNS Int AS BEGIN RETURN DateDiff(day, @BirthDate, @Today) / 365.25 END 

When a scalar function is called without specifying the owner or schema, SQL Server assumes it to be a built-in function in the system catalog. For this reason, user-defined scalar functions are always called using multi-part names, prefixed at least with the owner or schema name:

 SELECT dbo.fnGetAge('1/4/1962', GetDate()) 

Before writing the next sample function, I'd like to create a set of data to use. Assume that you are in charge of preparing invitations to your annual company picnic. The HR department manager has exported a list of employees from the personnel system to a text file. You have used DTS to import this data into SQL Server and now you need to format the data for the invitations. Names are in a single column in the form: LastName, FirstName. You need to separate the first name and last name values into two columns.

The business logic for parsing the last name and first name values is very similar. The logic for extracting the last name is as follows:

1. Find the position of the delimiting comma.

2. Identify the last name value from the first character through the character one position before the comma.

3. Return this value from the function.

Translating this logic into SQL, the function definition looks like this:

 CREATE FUNCTION fnLastName (@FullName VarChar(100)) RETURNS VarChar(100) AS BEGIN DECLARE @CommaPosition Int DECLARE @LastName VarChar(100) SET @CommaPosition = CHARINDEX(‘,’, SET @LastName = SUBSTRING(@FullName, 1, @CommaPosition - 1) RETURN @LastName END 

Two built-in functions are used. The CHARINDEX() function returns the position of a character string within another character string, in this case, the position of the comma within the full name. The SUBSTRING() function returns part of a character string from one character position to another. This will be used to carve the last name value from the full name. Because the last name ends one position before the comma, you subtract one from the value returned by the CHARINDEX() function.

If you execute this script, only the last name is returned, as shown in Figure 13-18.

Figure 13-18:

 SELECT dbo.fnLastName('Washington, George') 

Try It Out

Create two functions, one to parse the last name and another to parse the first name. Start by executing the script in the previous example. Next, create a new table and populate it with employee records:

 CREATE TABLE EmployeeList (EmployeeName VarChar(100)) GO INSERT INTO EmployeeList (EmployeeName) SELECT ‘Flintstone, Fred' INSERT INTO EmployeeList (EmployeeName) SELECT ‘Flintstone, Wilma' INSERT INTO EmployeeList (EmployeeName) SELECT ‘Flintstone, Pebbles' INSERT INTO EmployeeList (EmployeeName) SELECT ‘Rubble, Barmey' INSERT INTO EmployeeList (EmployeeName) SELECT ‘Rubble, Betty' INSERT INTO EmployeeList (EmployeeName) SELECT ‘Rubble, BamBam' 

The easiest way to create the first name function is to copy and paste the script and make a few modifications. The logic is similar to the first function but you want to start two characters after the comma to omit the space character. The SUBSTRING() function returns characters up to the end of the text. This means that if you provide a value greater than the remaining length of text, all characters to the right of the start position will be returned. The LEN() function ensures that this value always exceeds the number of available characters:

 CREATE FUNCTION fnFirstName (@FullName VarChar(100)) RETURNS VarChar(100) AS BEGIN DECLARE @CommaPosition Int DECLARE @FirstName VarChar(100) SET @CommaPosition = CHARINDEX(‘,’, SET @FirstName = SUBSTRING(@FullName, @CommaPosition + 2, LEN(@FullName)) RETURN @FirstName END 

Test the new function like before:

 SELECT dbo.fnFirstName('Washington, George') 

The result is shown in Figure 13-19.

Figure 13-19:

Finally, use both of these functions in a SQL statement, selecting rows from the table you already created. Remember that the table has only one column. Using each function, you will define two alias columns:

 SELECT dbo.fnLastName(EmployeeName) As LastName , dbo.fnFirstName(EmployeeName) As FirstName  FROM EmployeeList 

In the result set shown in Figure 13-20, the last and first names are separate and may be used in a form letter.

Figure 13-20:

Taking this example just one step further, these two functions can be combined into one by passing in a second parameter to indicate the name to extract:

 CREATE FUNCTION fnGetName (@FullName VarChar(100) , @FirstOrLast VarChar(5)) RETURNS VarChar(100) AS BEGIN DECLARE @CommaPosition Int DECLARE @TheName VarChar(100) IF @FirstOrLast = ‘First’ BEGIN SET @CommaPosition = CHARINDEX(‘,’, SET @TheName = SUBSTRING(@FullName, @CommaPosition + 2, LEN(@FullName)) END ELSE IF @FirstOrLast = ‘Last’ BEGIN SET @CommaPosition = CHARINDEX(‘,’, SET @TheName = SUBSTRING(@FullName, 1, @CommaPosition - 1) END RETURN @TheName END 

The new function is called just like before but with the addition of a second parameter, like this:

 SELECT dbo.fnFirstName('Washington, George', 'First') 

Inline Table-Valued Functions

This type of function returns a result set, much like a view. However, unlike a view, functions can accept parameters. The inline function's syntax is quite simple. In the function definition, the return type is set to a Table. A RETURN statement is used with a SELECT query in parentheses:

 CREATE FUNCTION fnProductListBySubCategory (@SubCategoryID Int) RETURNS Table AS RETURN ( SELECT ProductID, Name, ListPrice FROM Product WHERE ProductSubCategoryID = @SubCategoryID ) 

The function is treated almost like a table using the syntax Select ... From (function name):

 SELECT * FROM fnProductListBySubCategory(1) 

An added benefit is the ability to process business logic, as you would if using a stored procedure. This example shows the same function as before with an optional parameter and related conditional logic. If a subcategory ID value is provided, products are filtered by this value; otherwise, all products are returned.

 CREATE FUNCTION fnProductListBySubCategory (@SubCategoryID Int = Null) RETURNS Table AS BEGIN IF @SubCategoryID Is Null BEGIN RETURN ( SELECT ProductID, Name, ListPrice FROM Product ) END ELSE BEGIN RETURN ( SELECT ProductID, Name, ListPrice FROM Product WHERE ProductSubCategoryID = @SubCategoryID ) END END 

For returning a result set from a function, the inline table-valued function is likely the best choice. It's the most elegant blend of SELECT-compatible syntax with stored procedure style input parameters. Aside from these powerful capabilities, the syntax is simple and easy to manage.

Multi-Statement Table-Valued Functions

Multi-statement functions can be used to do some very unique things outside the context of a standard SELECT statement. Like the preceding inline function, this type of function also returns a table-type result set, but the table is explicitly constructed in script. This can be used to accomplish one of two things: either to process some very unique business logic by assembling a virtual table on-the-fly, or to duplicate the functionality of an inline function in a more verbose and complicated way. In short, if you need to select records from an existing table to return a result set, use an inline table-valued function.

The following is an example of the same function demonstrated in the previous section, as a multi-statement function. In the declaration, a table-type variable is used to define the return structure. In this case, the variable @ProdList defines a virtual table with three columns. The fact that these columns are the same as the corresponding columns in the Product table is purely a matter of choice. You can see that in the body of the function, I've inserted rows into the variable as if it were a physical table. Finally, the RETURN statement terminates execution and returns the result set:

 CREATE FUNCTION fnProductListBySubCategory (@SubCategoryID Int) RETURNS @ProdList Table (  ProductID Int , Name nVarChar(50) , ListPrice Money ) AS BEGIN IF @SubCategoryID IS NULL BEGIN INSERT INTO @ProdList (ProductID, Name, ListPrice) SELECT ProductID, Name, ListPrice FROM Product END ELSE BEGIN INSERT INTO @ProdList (ProductID, Name, ListPrice) SELECT ProductID, Name, ListPrice FROM Product WHERE ProductSubCategoryID = @SubCategoryID END RETURN END 

Now, I'll step out of the mainstream and show you a more unique application for this type of function. This function doesn't select data from a table. The records returned by this function are entirely manufactured within the script contained by the function. The filtering logic, implemented by the @Category parameter, accepts three relevant values: Mainframe, Micro, or All. If the value All is passed, rows for both of the previous categories are returned.

 CREATE FUNCTION fnComputerTypes(@Category VarChar(15))  Returns @CompType Table (  Year Int , BrandName VarChar(50) , ModelName VarChar(50) , Category VarChar(25) ) AS BEGIN IF @Category IN (‘MainFrame’, BEGIN INSERT INTO @CompType (Year, BrandName, ModelName, Category) SELECT 1945, ‘US Ordinance Dept.’, INSERT INTO @CompType (Year, BrandName, ModelName, Category) SELECT 1951, ‘Remington Rand’, INSERT INTO @CompType (Year, BrandName, ModelName, Category) SELECT 1952, ‘IBM’, INSERT INTO @CompType (Year, BrandName, ModelName, Category) SELECT 1964, ‘IBM’, INSERT INTO @CompType (Year, BrandName, ModelName, Category) SELECT 1988, ‘IBM’, END IF @Category IN (‘Micro’,  BEGIN INSERT INTO @CompType (Year, BrandName, ModelName, Category) SELECT 1977, ‘Tandy Radio Shack’, INSERT INTO @CompType (Year, BrandName, ModelName, Category) SELECT 1981, ‘Commodore ‘, ‘VIC-20’, INSERT INTO @CompType (Year, BrandName, ModelName, Category) SELECT 1982, ‘Commodore’, ‘Commodore INSERT INTO @CompType (Year, BrandName, ModelName, Category) SELECT 1981, ‘IBM’, INSERT INTO @CompType (Year, BrandName, ModelName, Category) SELECT 2004, ‘Dell’, ‘PowerEdge END RETURN END 

I'll test the function using the value All:

 SELECT * FROM dbo.fnComputerTypes('All') 

The result is shown in Figure 13-21.

Figure 13-21:

As far as the consumer of this data is concerned, it behaves like, and appears to have been selected from, a table in the database.