Understanding User-Defined Functions

When you start creating complex T-SQL programs or use T-SQL in another programming language such as Visual Basic, you will probably use the same calculations over and over again. Most languages give you the ability to define your own subroutines or functions to be reused in multiple places in your application. T-SQL is no different when it comes to defining your own functions. These user-defined functions provide you with the ability to create any type of function you might need. SQL Server 2000 supports three types of UDFs:

• Scalar functions

• Inline single table value functions

• Multi-statement table value functions

User-defined functions can be created to return either a scalar (single value) or a table (either single or multiple tables). In a UDF, the RETURNS clause determines whether the function is a scalar or a table function. When deciding what actions your functions will take, you should be aware of the SQL statements that you can use in a function. These include

• DECLARE statements (for defining data variables and cursors local to the function).

• Cursor operations that reference local cursors.

• FETCH statements that return data to the client are not allowed, only FETCH statements that assign values to a local variable can be used.

• Flow of control statements, such as BEGIN...END.

• Assignments of values to local objects.

• SELECT statements that contain select lists with expressions that assign values to local variables.

• UPDATE, INSERT, and DELETE statements that modify TABLE variables local to the function.

• EXECUTE statements that call an extended stored procedure.

The number of times that a function is actually executed in a query will vary between execution plans. An example of this is a function that is invoked by a subquery in a WHERE clause. The number of times the subquery and its function are executed can vary with different access paths chosen by the server.

When returning a single value, your function is considered a scalar function. If the UDF returns a table from a single SELECT statement, it is referred to as an inline table value function. If you create a table using this type of function, all the column names and data types are determined by the columns returned by the SELECT statement. If you specify new column names and different data types in your function, you are creating a multi-statement table value function.

Creating a Function

User-defined functions are created using the CREATE FUNCTION statement. Of course, there is no way for me to tell you what to create in a UDF. That is because a UDF is created to perform a unique function in an application. In the following sections, you will learn how to create all three of the different UDFs. In each, I will use a simple functional requirement as an example to show you how it is done.

You can create UDFs in the Query Analyzer by writing the CREATE FUNCTION code as described in the following sections. Or, you can choose to create UDFs in the Enterprise Manager by opening the database you are working with and then right-clicking on the User Defined Functions icon, which will display any UDFs already defined to the database. From the pop-up menu, select New User-Defined Function to display the User-defined Functions Properties dialog as shown in Figure 20.1.

This dialog form enables you to enter the code for the function, and then to verify the code by clicking the Check Syntax button. When you are satisfied with the function, click OK to create it.

Creating a Scalar Function

Creating a function might seem a bit confusing, so to start with, let's create a basic function that will return a single value. The scalar function is the easiest function to create and work with. The syntax to create a scalar function is shown along with Table 20.1 which lists the arguments and their descriptions.

CREATE FUNCTION [owner name.] function_name
([{@parameter_name [AS] scalar_parameter_data_type [ = default]}  [,...n]])
RETURNS scalar_return_data_type
[WITH ENCRYPTION | SCHEMABINDING]
[AS]
BEGIN
   <function body>
RETURN scalar_expression
						

Now that you have seen the syntax, let's create a simple UDF. Listing 20.1 shows you how to create a function that returns the last day of the month when you pass a date to the function.

Code Listing 20.1. Creating the Last Day of the Month Function

 1:Use Northwind
 2:Go
 3:Create Function fnLastDayMonth (@inDate datetime)
 4:Returns datetime
 5:As
 6:Begin
 7:  Declare @mm int
 8:  Declare @dd int
 9:  Declare @yy int
10:  Declare @tempdate datetime
11:  Declare @tempstr varchar(10)
12:
13:  Set @mm = Datepart(mm, @inDate)
14:  Set @dd = Datepart(dd, @inDate)
15:  Set @yy = Datepart(yy, @inDate)
16:
17:  If @mm = 12
18:     Begin
19:       Set @mm = 1
20:       Set @yy = @yy + 1
21:     End
22:  Else
23:     Begin
24:       Set @mm = @mm + 1
25:     End
26:
27:  Select @tempstr = Convert(varchar(2), @mm) +
28:         '/01/'+ Convert(varchar(4), @yy)
29:  Set @tempdate = Convert(datetime, @tempstr)
30:  Set @tempdate = Dateadd(dd, -1, @tempdate)
31:
32:  Return @tempdate
33:End
						

After you have entered the CREATE FUNCTION code and executed it, the function is available in the database as shown in Figure 20.2.

To execute the function, you simply reference it in a SELECT statement as shown.

Dbo.function_name()

Server: Msg 195, Level 15, State 10, Line 1

'fnlastdaymonth'is not a recognized function name.

Use Northwind
Select dbo.fnLastDayMonth('3/21/00') as 'EndOfMonth1'
Select dbo.fnLastDayMonth('2/31/00') as 'EndOfMonth2'
Select dbo.fnLastDayMonth('2/4/00') as 'EndOfMonth3'

The results you get from the preceding statements will probably surprise you. The first and third statements will execute correctly, but the second statement will cause an error. Here is the output:

EndOfMonth1
------------------------------------------------------
2000-03-31 00:00:00.000

(1 row(s) affected)

Server: Msg 242, Level 16, State 3, Line 2
The conversion of a char data type to a datetime
data type resulted in an out-of-range datetime value.
EndOfMonth3
------------------------------------------------------
2000-02-29 00:00:00.000

(1 row(s) affected)

Later in this section, I will briefly discuss how errors are handled in a User-Defined Function.

Creating a Single Table Value Function

The CREATE FUNCTION syntax changes a little when you are defining an inline table value function. The syntax for this statement is

CREATE FUNCTION [owner_name.] function_name
([{ @parameter_name scalar_parameter_data_type [ = default]}  [...,n]])
RETURNS TABLE



[WITH ENCRYPTION | SCHEMABINDING]
[AS]
RETURN [(] select_statement [)]

The big difference in the syntax is that the RETURNS clause specifies TABLE. This tells the server that instead of returning a single value, a table will be returned that is populated with the data returned from the specified SELECT statement. Of the two table functions you will look at in the next sections, the inline table value function is the easier of the two. It uses the same parameters as the scalar function with one exception: The select_statement is used instead of the scalar_expression. This is a single standard SELECT statement. Keep in mind that all columns in the SELECT statement must be named, including your computed columns.

In the following example, you will create a function that will return the total sales to date for a provided ProductID.

Use Northwind
Go
Create Function fnTotalSalesProduct (@inPrd int)
Returns Table As
Return (select productId,
        sum((quantity * unitprice) –
            ((quantity * unitprice) * discount)) as TotSales
        From [Order Details]
        Where ProductId = @inPrd
        group by productid)

When executing this UDF, you can reference it by itself or join with other tables using standard join syntax. The following two examples show you both of these options.

Select * from dbo.fnTotalSalesProduct(22)
Select fn.productid, productname, totsales
from dbo.fnTotalSalesProduct(21) as fn
     inner join products
     on products.productid = fn.productid

The output would look like this:

productId   TotSales
----------- ------------------
22          7122.3600082397461

(1 row(s) affected)

productid   productname           totsales
----------- --------------------- ------------
21          Sir Rodney's Scones   9104.0

(1 row(s) affected)

One last thing about an inline table value function. If you know that you will need data from two or more tables that you can join together, you could create the UDF with this join defined as shown in the modified sample function that follows. What you did here was move the join to the Products table to get the product name in the UDF, instead of doing the join later in the SELECT statement.

Use Northwind
Go
Create Function fnTotalSalesProduct (@inPrd int)
Returns Table As
Return (select od.productId,
        prd.productname,
        sum((od.quantity * od.unitprice) –
            ((od.quantity * od.unitprice) * od.discount)) as TotSales
        From [Order Details] as od inner join products as prd
        on prd.productid = od.productid
        Where od.ProductId = @inPrd
        group by od.productid, prd.productname)

The execution of this UDF would look like:

Select * from dbo.fnTotalSalesProduct(22)

With the output:

productId   productname           TotSales
----------- --------------------- ------------------
22          Gustaf's Knäckebröd   7122.3600082397461

(1 row(s) affected)
						

Creating a Multi-Table Function

A multi-table function differs from a scalar function in that you reference more than one table within the function itself. Creating a multi-table function is basically the same as creating a scalar function. Once again, the CREATE FUNCTION syntax changes slightly. The syntax shown here now requires you to define the structure of the table being returned.

CREATE FUNCTION [owner_name.] function_name
([{ @parameter_name scalar_parameter_data_type [ = default]}  [...,n]])
RETURNS @return_variable TABLE <table_type_definition>
[WITH ENCRYPTION | SCHEMABINDING]
[AS]
BEGIN
    function_body
    RETURN [(] select_statement [)]
END

In this type of UDF, the change to the syntax is a combination of both the scalar and inline table value functions. You create a SQL script that inserts data into a variable defined as a table data type. You use the multi-statement table value functions to return a table. However, the table that is returned does not need to be created by a single SELECT statement. For example, you might want to do some processing on data and create a temporary table. Then you can do some additional processing based on that temporary table and return a new table with your results.

To see how the multi-statement function works, you will create a function in the PUBS database that returns the royalties to date based on books sold. However, this is not as simple as it sounds. The problem is that a book might have more than one author. If a book has multiple authors who split their royalties, incorrect results would be returned if you simply calculated the royalty by row of data and returned it associated with each author.

To fix this problem, you should figure out the royalties by title, divide those royalties by the number of authors on each title, and return a table with the author's name, the title, and the newly modified royalties. You are assuming that the royalties are divided evenly among multiple authors.

To execute this UDF, you would use the following syntax:

SELECT * FROM dbo.fnRoyaltySplitByAuthor()

Server: Msg 208, Level 16, State 3, Line 1

Invalid object name 'dbo.fnRoyaltySplitByAuthor'.

The output of this SELECT statement is

au_fname         au_lname      title                     RoyToDate
---------------- ------------- ------------------------- ---------
Cheryl           Carson        But Is It User Friendly?  32240.16
...
Michael          O'Leary       Sushi, Anyone?            2046.135
Burt             Gringlesby    Sushi, Anyone?            2046.135
Akiko            Yokomoto      Sushi, Anyone?            2046.135

(25 row(s) affected)