In my last post, I showed you how to navigate a self-joined table to create a list of primary keys.  Now I will show you how to put the code into a UDF to return the list.

It is quite easy to create a User Defined Function that will return table variable.  Using the information from the last post, here is the setup.  Note that the table is defined right in the RETURN definition:

CREATE FUNCTION dbo.udf_BookingCommodityList
(
    @BookingId int
)
RETURNS @CommodityIdList TABLE (
    BookingCommodityId int )
AS 
BEGIN
<... Rest of function >

The cool thing about this is that, with the table defined in the RETURN definition, we can use it throughout the UDF as though we created it with a normal DECLARE statement.

So now that a table of primary keys is being returned, how can it be used.  Well it turns out that the UDF acts almost just like any other table in SQL Server.  For example, this statement works just fine:  SELECT * FROM dbo.udf_BookingCommodityList(12345).  This will return all the commodity ids for booking number 12345.

Using the UDF in a statement is pretty straight forward:

-- @BookingId is passed in as a parameter
SELECT commodity.Description
FROM dbo.udf_BookingCommodityList(@BookingId) bc
INNER JOIN Commodity ON (Commodity.BookingCommodityId = bc.BookingCommodityId)

This returns the commodity descriptions used on the booking.  This is great as long as the BookingId is known in advance (i.e. passed in as a parameter or derived in some other fashion; cursors, etc.).

This statement does not work to my dismay.  I haven't figured out why just yet.

SELECT commodity.Description
FROM Booking
INNER JOIN dbo.udf_BookingCommodityList(Booking.BookingId) bc
INNER JOIN Commodity ON (Commodity.BookingCommodityId = bc.BookingCommodityId)
WHERE Booking.BookingId = 12345