Professional SQL Server™ 2005 Programming

11.10. User-Defined Functions (UDFs)

Well, here we are already at one of my favorite topics. Five years after their introduction, user-defined functions — or UDFs — remain one of the more underutilized and misunderstood objects in SQL Server. In short, these were awesome when Microsoft first introduced them in SQL Server 2000, and the advent of .NET just adds some extra "oomph" to them.

11.10.1. What a UDF Is

A user-defined function is, much like a sproc, an ordered set of T-SQL statements that are pre-optimized and compiled and can be called to work as a single unit. The primary difference between them is how results are returned. Because of things that need to happen in order to support these different kinds of returned values, UDFs have a few more limitations to them than sprocs do.

OK, so I've said what a UDF is, so I suspect I ought to take a moment and say what it is not. A UDF is definitely NOT a replacement for a sproc — they are just a different option that offers us yet one more form of code flexibility.

There are two types of UDFs:

• Those that return a scalar value

• Those that return a table

Let's take a look at the general syntax for creating a UDF:

CREATE FUNCTION [<schema name>.]<function name> ( [ <@parameter name> [AS] [<schema name>.]<scalar data type> [ = <default value>] [ ,...n ] ] ) RETURNS {<scalar type>|TABLE [(<Table Definition>)]} [ WITH [ENCRYPTION]|[SCHEMABINDING]| [ RETURNS NULL ON NULL INPUT | CALLED ON NULL INPUT ] | [EXECUTE AS { CALLER|SELF|OWNER|<'user ...