Frequently Asked Questions about Microsoft SQL Server 2000
User-Defined Functions (UDF).
Sign Up for the T-SQL UDF of the Week or
visit the Archives.
Q: What is a UDF?
Q: Why would
you want to return a TABLE from a function?
A: UDF stands for User-Defined Function. UDF's are a feature of
Microsoft SQL Server 2000. They allow you to create functions that can be
embedded in other SQL statements. The functions return either scalar
values, such as varchar or int values, or they return tables.
A: There are two types of UDFs that return TABLES:
- The Inline UDF is very much like a SQL View.
- The Multi-Statement UDF is similar to a Stored Procedure.
An Inline UDF consists of a function declaration and a single SELECT
statement. The main difference between an Inline UDF and a VIEW is that the
Inline UDF can have parameters. The parameters can be used to modify the
results of the SELECT statement either by using them in expressions within the
SELECT list or by using them in conditions that appear in the WHERE or HAVING
A Multi-Statement UDF returns the contents of a TABLE variable that is
defined in the function declaration. The body of a Multi-Statement can
consist of many lines of T-SQL including it's flow of control structures.
This makes it very similar to stored procedures. However, there are
restrictions on which statements the UDF can execute. It can not modify
any data, other than the content's of it's own TABLE variables.
The advantage of a UDF over a stored procedure is that it can be included in
the FROM clause of a SQL Statement. For example:
FROM udf_MyUDF (37, 'Tuesday')
As you can see, the UDF takes parameters, which can be used to modify
the result set that the UDF returns. While a View can be used in the FROM
clause, it can't take parameters. A Stored Procedure can take parameters,
but it can't be used in the FROM clause. These advantages make UDFs that
return TABLES particularly useful.
Q: Why do you get an error message when you try to use getdate()
in a UDF?
A: UDFs don't allow the use of non-deterministic functions.
The workaround (a.k.a hack) involves creating a view that exposes
the function. See Transact-SQL
User-Defined Functions or
issue of the UDF of the Week.