SQL User-Defined Functions

Functions in programming languages are subroutines used to encapsulate frequently performed logic. Any code that must perform the logic incorporated in a function can call the function rather than having to repeat all of the function logic.

Microsoft® SQL Server™ 2000 supports two types of functions:

• Built-in functions

  Operate as defined in the Transact-SQL Reference and cannot be modified. The functions can be referenced only in Transact-SQL statements using the syntax defined in the Transact-SQL Reference. For more information about these built-in functions, see Using Functions.

• User-defined functions

  Allow you to define your own Transact-SQL functions using the CREATE FUNCTION statement. For more information about these built-in functions, see User-defined Functions.

User-defined functions take zero or more input parameters, and return a single value. Some user-defined functions return a single, scalar data value, such as an int, char, or decimal value.

For example, this statement creates a simple function that returns a decimal:

CREATE FUNCTION CubicVolume
-- Input dimensions in centimeters.
   (@CubeLength decimal(4,1), @CubeWidth decimal(4,1),
    @CubeHeight decimal(4,1) )
RETURNS decimal(12,3) -- Cubic Centimeters.
AS
BEGIN
   RETURN ( @CubeLength * @CubeWidth * @CubeHeight )
END

This function can then be used anywhere an integer expression is allowed, such as in a computed column for a table:

CREATE TABLE Bricks
   (
    BrickPartNmbr   int PRIMARY KEY,
    BrickColor      nchar(20),
    BrickHeight     decimal(4,1),
    BrickLength     decimal(4,1),
    BrickWidth      decimal(4,1),
    BrickVolume AS
              (
               dbo.CubicVolume(BrickHeight,
                         BrickLength, BrickWidth)
              )
   )

SQL Server 2000 also supports user-defined functions that return a table data type:

• A function can declare an internal table variable, insert rows into the variable, and then return the variable as its return value.
  
  
• A class of user-defined functions known as in-line functions, return the result set of a SELECT statement as a variable of type table.

These functions can be used in places where table expressions can be specified. For more information about the table data type, see Using Special Data.

User-defined functions that return a table can be powerful alternatives to views. A user-defined function that returns a table can be used where table or view expressions are allowed in Transact-SQL queries. Views are limited to a single SELECT statement; however, user-defined functions can contain additional statements that allow more powerful logic than is possible in views.

A user-defined function that returns a table can also replace stored procedures that return a single result set. The table returned by a user-defined function can be referenced in the FROM clause of a Transact-SQL statement, whereas stored procedures that return result sets cannot. For example, fn_EmployeesInDept is a user-defined function that returns a table and can be invoked by a SELECT statement:

SELECT *
FROM tb_Employees AS E,
     dbo.fn_EmployeesInDept('shipping') AS EID
WHERE E.EmployeeID = EID.EmployeeID

This is an example of a statement that creates a function in the Northwind database that will return a table:

CREATE FUNCTION LargeOrderShippers ( @FreightParm money )
RETURNS @OrderShipperTab TABLE
   (
    ShipperID     int,
    ShipperName   nvarchar(80),
    OrderID       int,
    ShippedDate   datetime,
    Freight       money
   )
AS
BEGIN
   INSERT @OrderShipperTab
        SELECT S.ShipperID, S.CompanyName,
               O.OrderID, O.ShippedDate, O.Freight
        FROM Shippers AS S
             INNER JOIN Orders AS O ON (S.ShipperID = O.ShipVia)
        WHERE O.Freight > @FreightParm
   RETURN
END

In this function, the local return variable name is @OrderShipperTab. Statements in the function build the table result returned by the function by inserting rows into the variable @OrderShipperTab. External statements invoke the function to reference the table returned by the function:

SELECT *
FROM LargeOrderShippers( $500 )