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 )