How to Code User-Defined Functions

In addition to the SQL Server functions you’ve learned about throughout this tutorial, you can also create your own functions, called user-defined functions. To do that, you use code that’s similar to the code you use to create a stored procedure. There are some distinct differences between stored procedures and user-defined functions, however. You’ll learn about those differences in the topics that follow.

Table below summarizes the three types of user-defined functions, also called UDFs, or just functions, that you can create using Transact-SQL. Scalar-valued functions are like the functions you learned about in chapter 9 that return a single value. In addition to scalar-valued functions, however, you can also create table-valued functions. As its name implies, a table-valued function returns an entire table. A table-valued function that’s based on a single SELECT statement is called a simple table-valued function. In contrast, a table-valued function that’s based on multiple SQL statements is called a multi-statement table-valued function.

Like a stored procedure, a UDF can accept one or more input parameters. The function shown in this section, for example, accepts a parameter named @ VendorName. However, a UDF can’t be defined with output parameters. Instead, the RETURN statement must be used to pass a value back to the calling program. The value that’s returned must be compatible with the data type that’s specified in the RETURNS clause. In this example, an integer that contains a VendorID value selected from the Vendors table is returned.

To call, or invoke, a scalar-valued function, you include it in an expression. Then, the value returned by the function is substituted for the function. The first SELECT statement in this section, for example, uses the value returned by the fnVendorID function in its WHERE clause. Note that when you refer to a user-defined function, you must include the name of the schema. In this case, the schema is dbo.

To invoke a table-valued function, you refer to it anywhere you would normally code a table or view name. The second SELECT statement in this section, for example, uses a function named fnTopVendorsDue in the FROM clause. You’ll see the definition of this function later in this chapter.

Unlike a stored procedure, a UDF can’t make permanent changes to the objects in a database. For example, it can’t issue INSERT, UPDATE, and DELETE statements against tables or views in the database. However, within the code for a function, you can create a table, a temporary table, or a table variable. Then, the function can perform insert, update, and delete operations on that table.

The three types of user-defined functions

Function TypeDescription
Scalar-valued functionReturns a single value of any T-SQL data type.
Simple table-valued functionReturns a table that's based on a single SELECT statement.
Multi-statement table-valued functionReturns a table that's based on multiple statements.

 

A statement that creates a scalar-valued function

 

A statement that invokes the scalar-valued function

 

A statement that invokes a table-valued function

 

Description

  • A user-defined function, also called a UDF or just a function, is an executable database object that contains SQL statements.
  • The name of a function can be up to 128 characters and is typically prefixed with the letters fn.
  • Functions always return a value. A scalar-valued function returns a single value of any T-SQL data type. A table-valued function returns an entire table.
  • A table-valued function can be based on a single SELECT statement, in which case it’s called a simple table-valued function, or it can be based on two or more statements, in which case it’s called a multi-statement table-valued function.
  • A function can’t have a permanent effect on the database. In other words, it can’t run an action query against the database.
  • You can call, or invoke, a scalar-valued function from within any expression. You can invoke a table-valued function anywhere you’d refer to a table or a view.
  • Unlike other database objects, you must specify the name of the schema when invoking a UDF.

Chapter: Code Stored Procedures, Functions, and Triggers