BlackWaspTM

This web site uses cookies. By using the site you accept the cookie policy.This message is for compliance with the UK ICO law.

SQL Server
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.

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.

SELECT * FROM JobListByJobName('Electrical Socket Failure')

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'
20 February 2010