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.
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 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
Argument | Description |
---|---|
Owner name | The object owner. |
Function_name | The name of the UDF. |
Parameter_name | The name for any parameters that you might need passed to the function. The maximum number of parameters is 1,024. |
Scalar_parameter_data_type | The data type of the return value of this function. Nonscalar data types are not supported. This includes TABLES, CURSORS, ROWVERSION, or TIMESTAMP. |
Scalar expression | SQL code that performs an action either by calculating or selecting data and returning a single value. |
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.
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.
Caution
When executing any user-defined function, you must specify the owner name as shown here:
Dbo.function_name()
Otherwise, you will get the following error message:
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.
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)
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()
Caution
Even if the function does not require any parameters, you must include the opening and closing parentheses. Otherwise, you will receive the following error message:
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)