Frequently Asked Questions

Sign Up for the Coding-in-SQL Newsletter or visit the On-line Issues.

Q: What is a UDF?
Q: Why would you want to return a TABLE from a function?

Q: What is a UDF?

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.

Q: Why would you want to return a TABLE from a function?

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 clause. 

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:

SELECT *
   FROM udf_MyUDF (37, 'Tuesday')
   WHERE ....

 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 this issue of the UDF of the Week.