Professional SQL Server™ 2005 Programming

11.11. UDFs That Return a Table

User-defined functions in SQL Server are not limited to just returning scalar values. They can return something far more interesting — tables. Now, while the possible impacts of this are sinking in on you, I'll go ahead and add that the table that is returned is, for the most part, usable much as any other table is. You can perform a JOIN against it and even apply WHERE conditions against the results. It's very cool stuff indeed.

To make the change to using a table as a return value is not hard at all — a table is just like any other SQL Server data type as far as a UDF is concerned. To illustrate this, we'll build a relatively simple one to start:

USE AdventureWorks
GO

CREATE FUNCTION dbo.fnContactList()
RETURNS TABLE
AS
RETURN (SELECT ContactID,
            LastName + ', ' + FirstName AS Name,
            EMailAddress AS email
        FROM Person.Contact)
GO

This function returns a table of SELECTed records and does a little formatting: joining the last and first names, separating them with a comma.

At this point, we're ready to use our function just as we would use a table:

SELECT *
FROM fnContactList()

Now, let's add a bit more fun into things. What we did with this table up to this point could have been done just as easily — easier in fact — with a view. But what if we wanted to parameterize a view? What if, for example, we wanted to accept last name input to filter our results (without having to manually put in our own WHERE clause)? It might look something like this: