By Karen Gayda
Microsoft added a host of new features to its SQL Server 2000 product, and
one of the most interesting for SQL programmers is the user-defined
function. Adding functions to the Transact SQL language has solved many
code reuse issues and provided greater flexibility when programming SQL
queries.
This article focuses on the syntax, structure, and application of Transact
SQL user-defined functions. The material presented is based on the SQL
Server 2000 Beta Release 2.
Types of Functions
SQL Server 2000 supports three types of functions: scalar, in-line table
functions, and multistatement table functions. All three types of
functions accept parameters of any scalar data type except rowversion.
Scalar functions return a single scalar value and in-line and
multistatement table functions return a table data type. (NOTE: the table
data type is new in SQL Server 2000.)
I. Scalar Functions
Scalar functions return a data type such as int, money, varchar, real, etc.
They can be used anywhere a built-in SQL function is allowed. The syntax
for a scalar function is the following:
CREATE FUNCTION [owner_name.] function_name ( [{ @parameter_name scalar_parameter_type [ = default]} [,..n]]) RETURNS scalar_return_type [WITH <function_option> >::={SCHEMABINDING | ENCRYPTION] [AS] BEGIN function_body RETURN scalar_expression END
A simple scalar function to cube a number would look like this:
CREATE FUNCTION dbo.Cube( @fNumber float)
RETURNS float
AS
BEGIN
RETURN(@fNumber * @fNumber * @fNumber)
END
Surprisingly, user-defined functions (UDFs) support recursion. Here is an
SQL Server 2000 UDF using the standard factorial example:
CREATE FUNCTION dbo.Factorial ( @iNumber int )
RETURNS INT
AS
BEGIN
DECLARE @i int
IF @iNumber <= 1
SET @i = 1
ELSE
SET @i = @iNumber * dbo.Factorial( @iNumber – 1 )
RETURN (@i)
END
II. In-Line Table Functions
In-line table functions are functions that return the output of a single
SELECT statement as a table data type. Since this type of function
returns a table, the output can be used in joins of queries as if it was a
standard table. The syntax for an in-line table function is as follows:
CREATE FUNCTION [owner_name.] function_name ( [{ @parameter_name scalar_parameter_type [ = default]} [,..n]]) RETURNS TABLE [WITH <function_option>::={SCHEMABINDING | ENCRYPTION}] RETURN [(] select_statement [)] An in-line function to return the authors from a particular state would look like this: CREATE FUNCTION dbo.AuthorsForState(@cState char(2) ) RETURNS TABLE AS RETURN (SELECT * FROM Authors WHERE state = @cState)
III. Multistatement Table Functions
Multistatement table functions are similar to stored procedures except that
they return a table. This type of function is suited to address situations
where more logic is required than can be expressed in a single query. The
following is the syntax for a multistatement table function:
CREATE FUNCTION [owner_name.] function_name ( [{ @parameter_name scalar_parameter_type [ = default]} [,..n]]) RETURNS TABLE [WITH <function_option> >::={SCHEMABINDING | ENCRYPTION] [AS] BEGIN function_body RETURN END
Hierarchical data, such as an organizational structure, is an example of
data that cannot be gathered in a single query. The Northwind Company
database's Employees table contains a field called ReportsTo that
contains the EmployeeID of the employee's manager. GetManagerReports
is a multistatement table function that returns a list of the employees who
report to a specific employee, either directly or indirectly.
CREATE FUNCTION dbo.GetManagerReports ( @iEmployeeID int ) RETURNS @ManagerReports TABLE ( EmployeeID int, EmployeeFirstName nvarchar(10), EmployeeLastName nvarchar(20), Title nvarchar(30), TitleOfCourtesy nvarchar(25), Extension nvarchar(4), ManagerID int ) AS BEGIN DECLARE @iRowsAdded int, — Counts rows added to — table with each iteration @PREPROCESSED tinyint, — Constant for record prior — to processing @PROCESSING tinyint, — Constant for record — being processed @POSTPROCESSED tinyint — Constant for records that — have been processed SET @PREPROCESSED = 0 SET @PROCESSING = 1 SET @POSTPROCESSED = 2 DECLARE @tblReports TABLE ( — Holds employees added with each pass thru source employees table EmployeeID int, EmployeeFirstName nvarchar(10), EmployeeLastName nvarchar(20), Title nvarchar(30), TitleOfCourtesy nvarchar(25), Extension nvarchar(4), ManagerID int, ProcessedState tinyint DEFAULT 0 ) –Begin by adding employees who report to the Manager directly. INSERT INTO @tblReports SELECT EmployeeID, FirstName, LastName, Title, TitleOfCourtesy, Extension, ReportsTo, @PREPROCESSED FROM Employees WHERE ReportsTo = @iEmployeeID –Save number of direct reports SET @iRowsAdded = @@ROWCOUNT — Loop through Employees table until no more iterations are necessary — (e.g., no more rows added) to add all indirect reports. WHILE @iRowsAdded > 0 BEGIN –Set just added employees ProcessedState to PROCESSING — (for first pass) UPDATE @tblReports SET ProcessedState = @PROCESSING WHERE ProcessedState = @PREPROCESSED –Add employees who report to Managers in — ProcessedState = PROCESSING INSERT INTO @tblReports SELECT e.EmployeeID, e.FirstName, e.LastName, e.Title, e.TitleOfCourtesy, e.Extension, e.ReportsTo, @PREPROCESSED FROM Employees e INNER JOIN @tblReports r ON e.ReportsTo = r.EmployeeID WHERE r.ProcessedState = @PROCESSING AND e.ReportsTo <> @iEmployeeID –Save number of rows added for this iteration SET @iRowsAdded = @@ROWCOUNT –Set ProcessedState to POSTPROCESSED for Managers whose –reports were added in this iteration UPDATE @tblReports SET ProcessedState = @POSTPROCESSED WHERE ProcessedState = @PROCESSING END –Save all data to output table INSERT INTO @ManagerReports SELECT EmployeeID, EmployeeFirstName, EmployeeLastName, Title, TitleOfCourtesy, Extension, ManagerID FROM @tblReports RETURN END
The output of this function would be used in the same manner as a standard
table. Figure 1 demonstrates JOINING the output of GetManagerReports with
the Employees table to produce a listing of the organizational structure of
the Northwind Company:
Figure 1: User function used in JOIN query.
Invoking Functions
There are a few syntax idiosyncrasies to observe when invoking user-defined
functions. SQL Server 2000 provides some system-level user-defined
functions in the Master database. These system functions are invoked with
a slightly different syntax than ones that you would create. System
functions that return a table have the following syntax:
::function_name ([argument_expr], [,…])
System functions that return a scalar value use this syntax:
function_name ([argument_expr], [,…])
User-created scalar and rowset functions are invoked in exactly the same
manner. The syntax for invoking a user-created function looks like this:
[database_name] owner_name. function_name ([argument_expr], [,…])
Limitations
User-defined functions do have some restrictions placed upon them. Not
every SQL statement or operation is valid within a function. The following
lists enumerate the valid and invalid function operations:
Valid:
- Assignment statements
- Control-flow statements
- Variable declarations
- SELECT statements that modify local variables
- Cursor operations that fetch into local variables
- INSERT, UPDATE, DELETE statement that act upon local table variables
Invalid:
- Built-in, nondeterministic functions such as GetDate()
- Statements that update, insert, or delete tables or views
- Cursor fetch operations that return data to the client
Performance Implications
Using UDFs will impact the performance of queries. The extent of the
performance impact depends upon how and where you use a user-defined
function. This is also true of built-in functions. However, UDFs have the
potential for more dramatic performance hits than built-in functions. You
should exercise caution when implementing functions in your queries and
perform benchmarking tests to insure that the benefits of using your
functions exceed the performance costs of using them.
Uses for Functions
Check constraints
Scalar user-defined functions can be used as check constraints for columns
in table definitions. As long as an argument to the function is a
constant or built-in function or an argument is the column being checked,
the function may be used to validate the column's value. These UDF
check constraints provide the ability to use more complex logic for
determining acceptable column values than Boolean expressions or LIKE
patterns would allow.
The following function validates that a serial number follows a specific
pattern and portions of the serial number match a specific algorithm for a
product type.
CREATE FUNCTION dbo.ValidSerialNumber( @nvcSerialNumber nvarchar(50)) RETURNS BIT AS BEGIN
DECLARE
@bValid BIT,
@iNumber INT
–default to invalid serial number
SET @bValid = 0
–Home Office Product
IF @nvcSerialNumber LIKE
'[0-9][A-Z][0-9][A-Z][0-9][0-9][0-9][0-9]'
BEGIN
SET @iNumber = CONVERT(int,RIGHT(@nvcSerialNumber,4))
IF @iNumber % 7 = 2
BEGIN
SET @bValid = 1
END
END
— Video Game
IF @nvcSerialNumber LIKE '[0-9][0-9][0-9][A-Z][0-9]5[A-Z]'
BEGIN
SET @iNumber = CONVERT(int,LEFT(@nvcSerialNumber, 3))
IF @iNumber % 2 = 0
BEGIN
SET @bValid = 1
END
END
RETURN ( @bValid)
END
CREATE TABLE dbo.CustomerProduct
(
CustomerID int NOT NULL PRIMARY KEY,
ProductID int NOT NULL,
SerialNumber nvarchar(20) NOT NULL
CHECK(dbo.ValidSerialNumber(SerialNumber) = 1)
)
Computed columns
Scalar functions can be used to compute column values in table definitions.
Arguments to computed column functions must be table columns, constants, or
built-in functions. This example shows a table that uses a Volume function
to compute the volume of a container:
CREATE FUNCTION dbo.Volume ( @dHeight decimal(5,2), @dLength decimal(5,2), @dWidth decimal(5,2) ) RETURNS decimal (15,4) AS BEGIN RETURN (@dHeight * @dLength * @dWidth ) END CREATE TABLE dbo.Container ( ContainerID int NOT NULL PRIMARY KEY, MaterialID int NOT NULL REFERENCES Material(MaterialID), ManufacturerID int NOT NULL REFERENCES Manufacturer(ManufacturerID) Height decimal(5,2) NOT NULL, Length decimal(5,2) NOT NULL, Width decimal(5,2) NOT NULL, Volume AS ( dbo.Volume( Height, Length, Width ) ) )
You should note that computed columns might be excluded from being indexed
if user-defined functions determine their value. An index can be created
on the computed column if the user-defined function is deterministic (e.g.,
always returns the same value given the same input).
Default constraints
Default column values can be set with user-defined functions. UDFs can be
very useful when a hard-coded value or built-in function does not suffice.
For example, if a doctor's office wished to save a patient's
appointment preference, a user-defined function could calculate the default
day and time in a function by using the current date/time when the
patient's record was created. If the patient's record were
created on a Friday at 10:34 AM the AppointmentPref column would default to
“Friday at 10:00” using the following function:
CREATE FUNCTION dbo.AppointmentPreference ( @dtDefaultDateTime datetime ) RETURNS nvarchar(50) AS BEGIN DECLARE @nDay nvarchar(10), @nHour nvarchar(6), @nPreference nvarchar(50), @tiHour tinyint –Get date description SET @nDay = DATENAME(dw, @dtDefaultDateTime ) –Find current hour SET @tiHour = DATEPART(hh,@dtDefaultDateTime) –Use only 12-hour times IF @tiHour > 12 BEGIN SET @tiHour = @tiHour – 12 END –Don't allow appointments during lunch IF @tiHour = 12 BEGIN SET @tiHour = 1 END — These are invalid hours IF @tiHour IN(5,6,7,8) BEGIN SET @tiHour = 4 END –Create preference text SET @nPreference = RTRIM(@nDay) + '''s at ' + CONVERT(varchar(2),@tiHour) + ':00' RETURN ( @nPreference) END CREATE TABLE dbo.Patient ( PatientID int NOT NULL PRIMARY KEY IDENTITY, FirstName nvarchar(20) NOT NULL, LastName nvarchar(20) NOT NULL, Addr1 nvarchar(50), Addr2 nvarchar(50), City nvarchar(50), State nvarchar(2), ZipCode nvarchar(20), HomePhone nvarchar(20), WorkPhone nvarchar(20), AppointmentPref nvarchar(50) DEFAULT (dbo.AppointmentPreference(GETDATE())) )
Assignments
Scalar user-defined functions can be used to assign values to scalar
variables. They may be used in any situation where a scalar built-in
function may be used.
DECLARE @fCube float SET @fCube = dbo.Cube( 4.5 )
Control flow
Scalar user-defined functions may be used to control program flow when used
in Boolean expressions.
IF dbo.ValidSerialNumber('002A15A') = 1 PRINT 'Yes' ELSE PRINT 'No'
Case expressions
User-defined functions that return a scalar value can be used in any of the
cases of CASE expressions. The following example uses the DailySpecial
function in a case function to determine what to display for a given day:
CREATE FUNCTION dbo.DailySpecial( @nvcDay nvarchar(10)) RETURNS NVARCHAR(100) AS BEGIN DECLARE @nvcSpecial nvarchar(100) SET @nvcDay = UPPER(@nvcDay) IF @nvcDay = 'SUNDAY' SET @nvcSpecial = 'Roast beef with green beans and baked potato' IF @nvcDay = 'MONDAY' SET @nvcSpecial = 'Chopped beef with green bean casserole' IF @nvcDay = 'TUESDAY' SET @nvcSpecial = 'Beef stew' IF @nvcDay = 'WEDNESDAY' SET @nvcSpecial = 'Beef pot pie' IF @nvcDay = 'THURSDAY' OR @nvcDay = 'FRIDAY' OR @nvcDay = 'SATURDAY' SET @nvcSpecial = 'Beef surprise' RETURN ( @nvcSpecial ) END –Use output of DailySpecial function SELECT Special = CASE DateName(dw, getdate()) WHEN 'Sunday' THEN dbo.DailySpecial('Sunday') WHEN 'Monday' THEN dbo.DailySpecial('Monday') WHEN 'Tuesday' THEN dbo.DailySpecial('Tuesday') WHEN 'Wednesday' THEN dbo.DailySpecial('Wednesday') ELSE 'It's a mystery!' END
Alternative to views
Rowset functions, functions that return tables, can be used as alternatives
to read-only views. Since views are limited to a single select statement,
user-defined functions can provide greater functionality than a view.
Powerful logic can be used when determining the records returned, which is
not possible within a view. Also, views cannot accept parameters so a separate view must be created if the WHERE clause must change for different search c
riteria.
Alternative to temporary tables
Rowset functions can be used as alternatives to temporary tables. For
example, if you wished to find authors in the Pubs database who sold no
books in a particular state, you could create a couple of functions that
would generate the desired resultset.
To find the quantity of books sold for a particular author in a given state
you could write the following function:
CREATE FUNCTION dbo.AuthorPoularityForState ( @cState Char(2)) RETURNS TABLE AS RETURN ( SELECT a.au_id, a.au_fname, a.au_lname, SUM(s.qty) AS QTY FROM Authors a INNER JOIN TitleAuthor ta ON a.au_id = ta.au_id INNER JOIN Titles t ON t.title_id = ta.title_id INNER JOIN Sales s ON s.title_id = t.title_id INNER JOIN Stores st ON st.Stor_ID = s.stor_id WHERE st.state = @cState GROUP BY a.au_id, a.au_fname, a.au_lname ORDER BY QTY DESC, a.au_lname, a.au_fname ) END
You could then create another function that would use the output from the
first function to find the authors in a particular state that have not had
any sales:
CREATE FUNCTION dbo.ReallyBoringAuthorsForState ( @cState Char(2) )
RETURNS TABLERETURN(
SELECT a.au_id as AuthorID, a.au_fname AS AuthorFirstName,
a.au_lname AS AuthorLastName, @cState AS State
FROM AuthorPopularityForState(@cState ) pa
RIGHT JOIN authors a ON pa.AuthorID = a.au_id
WHERE IsNull(pa.UnitsSold, 0) = 0)
The following SQL statement would list the California authors who had not
sold any books:
SELECT AuthorLastName, AuthorFirstName, AuthorID FROM ReallyBoringAuthorsForState('CA') ORDER BY AuthorLastName, AuthorFirstName
Figure 2: List of authors without book sales
Before the release of SQL Server 2000, temporary tables would likely have
been used to generate the interim data to be used for the final query
output. By using functions instead of temporary tables, potential table
name concurrency problems are avoided. Functions also offer greater code
reuse than temporary tables.
Conclusion
As demonstrated , user-defined functions provide many more programming
options than there were before UDFs were included in the Transact SQL
language. SQL Server programmers have waited a long time for the
user-defined functions Microsoft made possible with SQL Server 2000. The
advantages of code reusability and of fixing problems in a single routine
can now be realized by incorporating UDFs into our designs. Now if we
could only be given arrays in the next version.
About the Author
Karen Gayda is an independent software consultant from San Diego,
California. She specializes in Web application development using VB/COM,
SQL Server, DHTML, and scripting languages. She can be contacted at kgayda@yahoo.com.