SQL 2005+

SQL Server User-Defined Functions

by Richard Carr, published at http://www.blackwasp.co.uk/SQLUserFunctions.aspx

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.

Previous: Page 2

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.

SELECT * FROM ProfitPerCustomer()

Next: SQL Server Triggers

20 February 2010