Earlier in this tutorial we examined several different types of function, including aggregations, ranking functions, casting and conversion, mathematical functions, string processors and date and time functions. These functions accept zero or more parameters and return a single value. Sometimes you will find that the built-in functions do not meet your requirements. In these cases, you can create your own user-defined functions.
User-defined functions are similar to those provided by SQL Server as standard. They are given a name and, optionally, a set of parameters. User-defined functions are created for a database and can be used in scripts, stored procedures, triggers and other user-defined functions that are defined within the database. As with stored procedures, they help to modularise your Transact-SQL (T-SQL) code and improve maintainability by allowing you to centralise logic. For example, you could create a function that returns a standard tax rate. If the tax rules change, the function can be modified and all T-SQL that utilises the function would use the new tax rate automatically.
User-defined functions can be separated into two main categories. These are scalar functions and table-valued functions.
Scalar functions are similar to the functions we have used in earlier articles. They can optionally accept one or more parameters and return a single value. The return value is a standard data type but cannot be a Text, NText, Image or Timestamp.
The second type of user-defined function is the table-valued function. As the name suggests, a table-valued function returns a table variable. Table-valued functions are often used as "parameterised views" as they can be included in place of tables in a query and can accept arguments. Such queries select from the rows provided in the function's result.
Table-valued functions can be further categorised into inline and multi-function variations. These determine the syntax used when creating the function but do not change the way in which it is called. Inline functions contain a single statement, which must be a SELECT. The results of the query become the return value of the function.
Multi-function table-valued functions can include many statements, similar to those in a stored procedure. The table structure to be returned is defined within the function, rather than being implicitly created to hold a query's results. A batch of statements is used to populate the table variable before it is returned.
Creating User-Defined Functions
In this article we will create some user-defined functions in the JoBS database. This is the database that we have created throughout the course of the tutorial. You can download a script to generate the database using the link at the top of this page.
Creating a Simple Scalar Function
The first function that we will create will be a simple scalar function that returns a tax rate. This could be used throughout a database when calculating the tax on sales. If the government changes the tax rate, it would require a single change to modify all of the functionality in the database that uses the percentage. As the rate is fixed, no parameters are required for this function.
The syntax for creating a function with no parameters is as follows:
CREATE FUNCTION function-name()
The signature of the function contains two key elements. The function-name is a unique name for the new function and return-type defines the data type that will be returned. The function's body appears between BEGIN and END keywords and can include multiple statements. The return-value is the value that the function returns to its caller and must be of the type declared in the signature.
To create the simple TaxRate function and set the value that it returns to 17.5, execute the following:
CREATE FUNCTION TaxRate()
Calling a Function
User-defined scalar functions can be used in the same places you might use built-in functions. These include within scripts, queries, INSERT and UPDATE commands and stored procedures. To simply show the value returned by the TaxRate function, we can use the PRINT command as follows:
PRINT dbo.TaxRate() -- 17.50
Note the inclusion of "dbo." as a prefix to the function name. This is the schema name and must be provided for scalar functions. Schema names are used to separate elements of a database and provide different schemas for different users. In the JoBS database we have only used the default schema, named "dbo".
20 February 2010