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.

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:

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

Adding Parameters

Most functions will include parameters that determine the operation of the function and the eventual return value. Parameters can be added as a comma-separated list within the parentheses that follow the function name. The syntax is very similar to that of stored procedures.

The following function adds a single parameter that accepts a monetary value. It performs a calculation that adds the tax rate, defined in the earlier function, and returns the result.

CREATE FUNCTION ValueWithTax (@Value MONEY)
RETURNS MONEY
AS
BEGIN
    RETURN @Value + (@Value * dbo.TaxRate() / 100)
END

To demonstrate the new function, execute the following query. This returns all of the contracts from the database, showing the contract number, contract value and the value with the tax rate added.

SELECT ContractNumber, ContractValue, dbo.ValueWithTax(ContractValue) AS WithTax
FROM Contracts

Using Data

The body of a user-defined function can include statements that read data from tables, views or other functions. This allows the creation of very powerful, reusable functions for a database. In the following example, the function accepts three parameters. The first parameter is used to specify the ID of an engineer, which must match the primary key value of a row in the Engineers table. The next two parameters allow the specification of a number of hours of normal and overtime working. The function reads the engineer's pay rates and calculates the amount earned accordingly.

CREATE FUNCTION EngineerCost
(
    @EngineerId INT,
    @NormalHours NUMERIC(4, 2),
    @OvertimeHours NUMERIC(4, 2)
)
RETURNS MONEY
AS
BEGIN
    DECLARE @HourlyRate MONEY
    DECLARE @OvertimeRate MONEY

    SELECT
        @HourlyRate = HourlyRate,
        @OvertimeRate = OvertimeRate
    FROM
        Engineers
    WHERE
        EngineerId = @EngineerId

    RETURN (@HourlyRate * @NormalHours) + (@OvertimeRate * @OvertimeHours)
END

In the JoBS database, Engineer 1 earns £19.75 per standard hour and £29.63 per hour at the overtime rate. Execute the following three statements to see this data used to calculate sample payments.

PRINT dbo.EngineerCost(1, 10, 0)    -- 197.50
PRINT dbo.EngineerCost(1, 0, 10)    -- 296.30
PRINT dbo.EngineerCost(1, 10, 10)   -- 493.80

Creating an Inline Table-Valued Function

Inline table-valued functions are generally used to create "parameterised views". They do not include statements in a BEGIN / END block. Instead, the body of the function is a SELECT statement that may use the values passed in as arguments. The syntax for an inline table-valued function is as follows:

CREATE FUNCTION function-name
(
    param-1
    param-2
    .
    .
    param-X
)
RETURNS TABLE
AS
RETURN
    query

We can create a simple inline function using the following script. This function retrieves a list of jobs that have the same name. The name of job to locate is supplied using the parameter.

CREATE FUNCTION JobListByJobName (@JobName VARCHAR(100))
RETURNS TABLE
AS
RETURN
    SELECT * FROM JobList
    WHERE JobName = @JobName

Once created, a table-valued function can be used as the data source in a SELECT statement. For example, the following query retrieves all of the jobs where the JobName column contains "Electrical Socket Failure". NB: You do not need to include the schema name prefix for table-valued functions.

As the function is simply replacing a table or view in the query, other clauses can be added. The following example retrieves jobs that are electrical socket failures repaired on 2 February 2008.

SELECT * FROM JobListByJobName('Electrical Socket Failure')
WHERE VisitDate = '2008-03-02'

Creating a Multi-Statement Table-Valued Function

Multi-statement table-valued functions can be used to create very powerful functions that return table data. They include a set of statements contained between BEGIN and END keywords. The body can read information from the database and perform many other tasks that you could include in stored procedures.

When defining a multi-statement function, the return value is declared as a table variable and includes the full structure of the table that will be returned. The body of the function performs actions that fill the table variable with data. At the end of the processing, the RETURN statement is used without a value and the previously declared table variable is returned.

Earlier in the tutorial we created a stored procedure that used a cursor and a temporary table to build a list of customers and the profit that each had provided to the company. The stored procedure returned data but in a format that would be difficult to process further with T-SQL. We can gain flexibility by recreating this stored procedure as a function:

CREATE FUNCTION ProfitPerCustomer()
RETURNS @CustomerValue TABLE
(
    CustomerNumber INT,
    TotalContractValue MONEY,
    EngineerCost MONEY,
    PartsCost MONEY,
    Profit MONEY
)
AS
BEGIN
    DECLARE @CustomerNumber INT
    DECLARE @TotalContractValue MONEY
    DECLARE @EngineerCost MONEY
    DECLARE @PartsCost MONEY

    INSERT INTO @CustomerValue (CustomerNumber)
    SELECT CustomerNumber FROM Customers

    DECLARE CustomerCursor CURSOR FOR
    SELECT CustomerNumber FROM @CustomerValue

    OPEN CustomerCursor

    FETCH NEXT FROM CustomerCursor
    INTO @CustomerNumber

    WHILE @@FETCH_STATUS = 0
    BEGIN

        SELECT @TotalContractValue = sum(ContractValue) FROM Contracts C
        INNER JOIN CustomerAddresses A ON C.CustomerAddressId = A.AddressId
        WHERE A.CustomerNumber = @CustomerNumber

        SELECT @EngineerCost = sum(J.Duration * E. HourlyRate) FROM Jobs J
        INNER JOIN Engineers E ON J.EngineerId = E.EngineerId
        INNER JOIN Contracts C ON J.ContractNumber = C.ContractNumber
        INNER JOIN CustomerAddresses A ON C.CustomerAddressId = A.AddressId
        WHERE A.CustomerNumber = @CustomerNumber

        SELECT @PartsCost = sum(TotalCost) FROM UsedParts P
        INNER JOIN Jobs J ON P.JobId = J.JobId
        INNER JOIN Contracts C ON J.ContractNumber = C.ContractNumber
        INNER JOIN CustomerAddresses A ON C.CustomerAddressId = A.AddressId
        WHERE A.CustomerNumber = @CustomerNumber

        UPDATE
            @CustomerValue
        SET
            TotalContractValue = isnull(@TotalContractValue, 0),
            EngineerCost = isnull(@EngineerCost, 0),
            PartsCost = isnull(@PartsCost, 0)
        WHERE
            CustomerNumber = @CustomerNumber	

        FETCH NEXT FROM CustomerCursor
        INTO @CustomerNumber
    END

    CLOSE CustomerCursor
    DEALLOCATE CustomerCursor

    UPDATE
        @CustomerValue
    SET
        Profit = TotalContractValue - EngineerCost - PartsCost

    RETURN
END

With the function created, the data can be used as if it were in a table. The statement below returns the data from the function. We could add further clauses to filter, sort, group and summarise this information.