Like this article? We recommend
Like this article? We recommend
Like this article? We recommend
UDF Examples
In this section, I will present a few UDFs to help you get some idea of what you can accomplish with various sorts of UDFs.
The following scalar function returns a maximum amount of books sold for a specified title. This function should be executed in pubs database. If the title has no sales, the UDF will return zero.
/* scalar function to return max amount of books sold
** for the specified title_id
*/
CREATE FUNCTION dbo.udf_max_copies_sold_for_title (@title_id CHAR(6))
RETURNS INT
AS
BEGIN
DECLARE @qty INT
-- initialize the variable at 0:
SELECT @qty = 0
SELECT
@qty = MAX(qty)
FROM sales
WHERE
title_id = @title_id
/* If there are no books sold for title_id specified
** then return 0:
*/
RETURN ISNULL(@qty, 0)
END
Now we can execute this function as follows:
SELECT dbo.udf_max_copies_sold_for_title ('bu2075')
Results:
----------- 55
The following in-line function returns the product name, quantity ordered, and supplier for the top five best sellers in a particular year in the Northwind database:
/* in-line function to return quantity, product name and supplier
** for the top 5 best-selling products within the specified year
*/
CREATE FUNCTION dbo.udf_top_5_best_sellers (@year INT)
RETURNS TABLE
AS
RETURN
SELECT TOP 5
SUM(quantity) AS quantity_ordered,
a.ProductID,
ProductName,
CompanyName AS Supplier
FROM [order details] a INNER JOIN products b
ON a.productid = b.productid
INNER JOIN suppliers c ON c.supplierid = b.supplierid
INNER JOIN orders d ON d.orderid = a.orderid
AND DATEPART(YEAR, OrderDate) = @year
GROUP BY a.productid, productname, CompanyName
ORDER BY 1 DESC
We can execute this function as follows:
SELECT * FROM dbo.udf_top_5_best_sellers (1998)
Results:
|
quantity_ordered |
ProductID |
ProductName |
Supplier |
|
659 |
13 |
Konbu |
Mayumi's |
|
546 |
24 |
Guaraná Fantástica |
Refrescos Americanas LTDA |
|
542 |
60 |
Camembert Pierrot |
Gai pturage |
|
513 |
59 |
Raclette Courdavault |
Gai pturage |
|
414 |
7 |
Uncle Bob's Organic Dried Pears |
Grandma Kelly's Homestead |
Alternatively, we can SELECT only the desired columns from this function:
SELECT ProductID,
ProductName
FROM dbo.udf_top_5_best_sellers (1998)
Results:
|
ProductID |
ProductName |
|
13 |
Konbu |
|
24 |
Guaraná Fantástica |
|
60 |
Camembert Pierrot |
|
59 |
Raclette Courdavault |
|
7 |
Uncle Bob's Organic Dried Pears |
The following multi-statement function accepts a delimited list as a parameter parses the string and returns the table containing each of the values in the parameter.
/* multi-statement function to parse
** a delimited list and turn it into a rowset
** DEFAULT delimiter is comma
*/
CREATE FUNCTION dbo.parse_comma_delimited_integer(
@list VARCHAR(8000),
@delimiter VARCHAR(10) = ',')
-- table variable that will contain values
RETURNS @tablevalues TABLE (
item INT)
AS
BEGIN
DECLARE @item VARCHAR(255)
/* Loop over the commadelimited list */
WHILE (DATALENGTH(@list) > 0)
BEGIN
IF CHARINDEX(@delimiter,@list) > 0
BEGIN
SELECT @item =
SUBSTRING(@list,1,(CHARINDEX(@delimiter, @list)-1))
SELECT @list =
SUBSTRING(@list,(CHARINDEX(@delimiter, @list) +
DATALENGTH(@delimiter)),DATALENGTH(@list))
END
ELSE
BEGIN
SELECT @item = @list
SELECT @list = NULL
END
-- Insert each item into temp table
INSERT @tablevalues (
item)
SELECT item = CONVERT(INT, @item)
END
RETURN
END
Now we can execute this function as follows:
SELECT * FROM dbo.parse_comma_delimited_integer
('39, 549, 324, 3556, 24, 2132, 345', ',')
Results:
|
item |
|
39 |
|
549 |
|
324 |
|
3556 |
|
24 |
|
2132 |
|
345 |
The following multi-statement UDF written for the Northwind database finds customers who have bought products in large quantities during a given year. The UDF accepts the year and amount of total sales and returns the customer name, the product that they have bought in large quantities, and all sales representatives who interfaced with these customers within the given year:
CREATE FUNCTION dbo.udf_top_customers_and_reps (
@year INT,
@amount INT)
RETURNS
@temp TABLE(
ProductName VARCHAR(200),
CategoryName VARCHAR(200),
CustomerID CHAR(5),
CompanyName VARCHAR(200),
TotalSales INT,
EmployeeNames VARCHAR(2000))
AS
BEGIN
/* populate the temp table with customers that have purchased any product
* with total sales greater than the specified amount, within the given year
*/
INSERT @temp (
ProductName ,
CategoryName ,
CompanyName ,
CustomerID ,
TotalSales
)
SELECT
ProductName,
CategoryName,
CompanyName,
b.CustomerID,
SUM(a.UnitPrice * quantity) AS total_sales
FROM [order details] a
INNER JOIN orders b ON a.orderid = b.orderid
INNER JOIN products c ON c.productid = a.productid
INNER JOIN customers d ON d.customerid = b.customerid
INNER JOIN categories e ON e.CategoryID = c.CategoryID
WHERE DATEPART(YEAR, OrderDate) = @year
GROUP BY c.ProductName, e.CategoryName, b.CustomerID,
d.CompanyName, DATEPART(YEAR, OrderDate)
HAVING SUM(a.UnitPrice * quantity) > @amount
ORDER BY ProductName
/* now get all the employees that have been involved with the customers
* in the given year and return them in a comma-delimited list
*/
DECLARE @CustomerID CHAR(5),
@EmployeeName VARCHAR(200)
DECLARE @Employees TABLE (
EmployeeName VARCHAR(80))
DECLARE CustomerCursor CURSOR FOR
SELECT CustomerID
FROM @temp a
OPEN CustomerCursor
FETCH NEXT FROM CustomerCursor INTO @CustomerID
WHILE @@FETCH_STATUS = 0
BEGIN
INSERT @employees
SELECT DISTINCT FirstName + ' ' + LastName
FROM Employees a INNER JOIN Orders b ON a.EmployeeID = b.EmployeeID
AND DATEPART(YEAR,OrderDate) = @year
WHERE b.CustomerID = @CustomerID
/* create a comma-delimited list of employees */
SELECT @EmployeeName = ''
SELECT @EmployeeName = @EmployeeName + ', ' + EmployeeName
FROM @Employees
SELECT @EmployeeName = SUBSTRING(@EmployeeName, 3, LEN(@EmployeeName)-2)
UPDATE @temp
SET EmployeeNames = @EmployeeName
WHERE CustomerID = @CustomerID
DELETE @Employees
FETCH NEXT FROM CustomerCursor INTO @CustomerID
END
CLOSE CustomerCursor
DEALLOCATE CustomerCursor
RETURN
END
The following execution of the function returns customers who have bought any product in amounts of over $10,000 during 1997:
SELECT * FROM dbo.udf_top_customers_and_reps (1997, 10000)
Results:
|
ProductName |
CategoryName |
CustomerID |
Companyname |
TotalSales |
EmployeeNames |
|
Côte de Blaye |
Beverages |
MEREP |
Mère Paillarde |
10329 |
Janet Leverling, Laura Callahan, Margaret Peacock, Michael Suyama, Nancy Davolio, Robert King |
|
Côte de Blaye |
Beverages |
SIMOB |
Simons bistro |
10540 |
Andrew Fuller, Margaret Peacock, Robert King |