SQL 2005+

SQL Server User-Defined Functions

The fifty-ninth part of the SQL Server Programming Fundamentals tutorial examines the creation of user-defined functions. These are custom functions that can be used within scripts and procedures in the same manner as standard functions.

User-Defined Functions

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

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.

Table-Valued Functions

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.