Guru's Guide to SQL Server™ Stored Procedures, XML, and HTML, The

Table-Valued Functions

Table-valued functions are also quite powerful and relatively easy to use. Table-valued functions return a table data type as their function result (UDFs do not support output parameters). With a few exceptions, you can perform the same operations on this table that you can perform on a regular table, including inserting, deleting, and updating (you can't use INSERT…EXEC or use user-defined data types). Text columns are automatically flagged as text in row columns. Here's an example of a multistatement table-valued UDF (Listing 10-2):

Listing 10-2. A basic table-valued function.

CREATE TABLE staff (employee int PRIMARY KEY, employee_name varchar(10), supervisor int NULL REFERENCES staff (employee)) INSERT staff VALUES ...