<code>

CREATE PROCEDURE simple_ap

( @cityID integer )

AS

       SELECT CustomerID, CompanyName

       FROM Customers

       WHERE Customers.CityID = @cityID

</code>

The result is a recordset with two columns and zero or more rows.

Unfortunately, there is no easy way to re-use the results of such a stored procedure. You can’t feed the recordset to another procedure, for example. Nor can you JOIN the results to a table, view or another recordset. If you could, then your opportunities to re-use code would multiply significantly, and you could design your code with this in mind.

Enter the table UDF. This construct enables you to treat the result set just as if it were a genuine table or view – you can JOIN it to other objects, add a WHERE clause to it, and much more.

In SQL 2000, the syntax to create a table UDF is:

<code>

CREATE FUNCTION [owner].[function_name]

(

       @parm1 <datatpe> = <default>

)

RETURNS TABLE

AS

       RETURN

       (

       SELECT <column1, column2, ...>

       FROM <table, view, etc.>

       WHERE <some condition applies>   -- optional clauses

)

</code>

To turn the stored procedure above into a table UDF, just substitute the appropriate chunks for the place markers in the UDF syntax, so that it looks like this:

<code>

CREATE FUNCTION [dbo].[Customers_By_City_Select]    -- denotes table UDF

(

       @city nvarchar(15)

)

RETURNS TABLE

AS

       RETURN

       (

       SELECT CustomerID, CompanyName

       FROM dbo.Customers

       WHERE Customers.City = @city

       )

</code>

The result set is identical, but with some cool new advantages. First, using the procedure instead, a call would look like:

<code>

EXEC simple_ap 12345

</code>

A call to the UDF would look like this:

<code>

SELECT * FROM Customers_By_City_Select ('Berlin')

</code>

Note that using the UDF, we can add the word DISTINCT to the call, which I did to reduce the result set here:

<code>

ALFKI  Alfreds Futterkiste

ANATR  Ana Trujillo Emparedados y helados

ANTON  Antonio Moreno Taquería

AROUT  Around the Horn

</code>

   This not only demonstrates that a table function is for all intents an actual table. A procedure that allowed this flexibility would have to be more complicated.

The fun is just beginning. We can select any subset of the columns returned by the function:

<code>

SELECT CustomerID FROM Customers_By_City_Select ('Berlin')

</code>

The coolest aspect of table functions is their table-ness: you can JOIN to tables, views and other table functions, referring to the UDF just as if it were a table:

<code>

SELECT Customers_By_City_Select.CustomerID, OrderID 

FROM Customers_By_City_Select ('Berlin')

INNER JOIN Orders ON Orders.CustomerID = Customers_By_City_Select.CustomerID</code>

</code>

This returns the following result set:

<code>

ALFKI  10643

ALFKI  10692

ALFKI  10702

ALFKI  10835

ALFKI  10952

ALFKI  11011

</code>

This ability facilitates what I like to call atomic queries. An atomic query returns results from one table (or view). By combining several atomic queries, you can create a molecular query (consisting of several atoms). Table UDFs enable you to reach a level of granularity and reusability that is impossible to achieve using stored procedures.