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