Page 82 - DCAP508_DATABASE_ADMINISTRATION
P. 82
Database Administration
Notes
UDFs can also return tabular data. For instance, we can create a UDF that returns products that
relates to a specific category:
CREATE FUNCTION dbo.udf_GetProductsByCategoryID
(
@CategoryID int
)
RETURNS TABLE
AS
RETURN
(
SELECT ProductID, ProductName, SupplierID, CategoryID, QuantityPerUnit, UnitPrice,
UnitsInStock, UnitsOnOrder, ReorderLevel, Discontinued FROM Products WHERE CategoryID
= @CategoryID
)
The udf_GetProductsByCategoryID UDF accepts a @CategoryID input parameter and returns
the results of the particular SELECT query. Once created, this UDF can be referenced in the
FROM (or JOIN) clause of a SELECT query. The example given below would return the ProductID,
ProductName, and CategoryID values for all of the beverages.
SELECT ProductID, ProductName, CategoryID
FROM dbo.udf_GetProductsByCategoryID(1)
76 LOVELY PROFESSIONAL UNIVERSITY
