SQL 2005+

SQL Server User-Defined Functions (2)

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.

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()
RETURNS return-type
AS
BEGIN
    statement-1
    statement-2
    .
    .
    statement-X
    RETURN return-value
END

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()
RETURNS NUMERIC(5,2)
AS
BEGIN
    RETURN 17.5
END

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".

Modifying a Function

To modify an existing function you can use the ALTER FUNCTION command. The syntax for the command is identical to that of the CREATE FUNCTION command. For example, if the tax rate were to increase from 17.5% to 18.75%, the function could be updated as follows:

ALTER FUNCTION TaxRate()
RETURNS NUMERIC(5,2)
AS
BEGIN
    RETURN 18.75
END