Advantages of UDFs
So why are UDFs important? What can you do with UDFs that you cannot with stored procedures? Well, UDFs are not functionally stronger than stored procedures (in fact, UDFs have many limitations, which we will examine shortly). However, UDFs do provide certain advantages, discussed in the following sections.
Execution Within the SELECT Statement
UDFs are executed within an inline query; for instance, you can call a scalar UDF that calculates the number of business days between two dates, as follows:
SELECT dbo.no_of_business_days(date1, date2)The developer's ability to execute UDF within inline queries makes UDF easier to use than a procedure within other routines. If you had to perform the same calculation in a procedure, you'd have to return the value as an output parameter. Using output parameters isn't necessarily cumbersome, but would involve more coding, as follows:
DECLARE @no_of_business_days INT
EXEC dbo.proc_for_calculating_business_days date1, date2, @no_of_business_days OUTPUTExecution from Various Parts of SQL Statements
Scalar UDFs can be called from within the WHERE/HAVING clause of queries or even within a SET list of the UPDATE statement. For instance, the following UDF determines the total sales for a given customer within a particular year by using the Northwind database:
CREATE FUNCTION dbo.total_orders (@customerid CHAR(6), @year INT)
RETURNS INT
AS
BEGIN
DECLARE @total INT
SELECT @total = SUM(unitprice * quantity)
FROM [order details] a INNER JOIN orders b
ON a.orderid = b.orderid
WHERE
DATEPART(YEAR, orderdate) = @year
AND
customerid = @customerID
RETURN @total
ENDThis function can be executed within a WHERE clause of the query returning customers who have purchased goods for more than $15,000 in 1996:
SELECT DISTINCT CustomerID
FROM
orders a INNER JOIN [order details] b
ON a.orderid = b.orderid
WHERE
dbo.total_orders(customerID, 1996) > 15000Results:
CustomerID
ERNSH
UDFs can also be used within a CASE statement; for instance, you can use total_orders UDF to rank customers according to their sales in 1996, as follows:
SELECT CompanyName,
SalesRank_1996 = CASE
WHEN dbo.total_orders(customerID, 1996) < 1000 THEN 'poor'
WHEN dbo.total_orders(customerID, 1996) BETWEEN 1001 AND 5000 THEN 'average'
WHEN dbo.total_orders(customerID, 1996) BETWEEN 5001 AND 13000 THEN 'good'
ELSE 'super'
END
FROM customersResults (abbreviated list):
CompanyName SalesRank_1996
---------------------------------------- --------------
Alfreds Futterkiste super
Ana Trujillo Emparedados y helados poor
Antonio Moreno Taquería poor
Around the Horn average
Berglunds snabbköp average
Blauer See Delikatessen super
Blondesddsl père et fils good
Bólido Comidas preparadas poor
Bon app' average
Bottom-Dollar Markets average
UDF Output Can Be Used as a Rowset
UDFs that return tables can be treated as another rowset. Thus, you can join other tables within your code module to the output of a UDF. For example, if you have a UDF returning two best-selling titles along with their authors, you can easily join it with another table, as follows:
SELECT
a.title,
a.author_name,
b.royalty_amount
FROM dbo.udf_bestselling_titles a INNER JOIN royalty b
ON a.author_id = b.author_idIf you had to use a stored procedure to do the same procedure, you'd have to do the following:
Define a temporary table with the CREATE TABLE statement.
Populate the temporary table with the output of the stored procedure.
Join the temporary table to other tables.
Drop the temporary table at the end of the routine.
Here is the code for a solution that uses a stored procedure instead of a UDF:
CREATE TABLE #temp_table (
Title_id INT,
Title VARCHAR(200),
Author_name VARCHAR(45)
)
INSERT #temp_table (
title_id,
title,
author_name )
EXECUTE dbo.usp_bestseller_titles
SELECT
a.title,
a.author_name,
b.royalty_amount
FROM #temp_table a INNER JOIN royalty b
ON a.author_id = b.author_iUDFs as Parameterized Views
In-line UDFs can be thought of as views that accept parameters. This flavor of UDFs can be used effectively to return different data from the same tables based on the parameter values. For instance, the following UDF will return different categories of titles based on the supplied parameter:
CREATE FUNCTION dbo.udf_category_titles (@category VARCHAR(12))
RETURNS TABLE
AS
RETURN (
SELECT title, pub_id, price FROM titles
WHERE
type = @category)In-line UDFs can also be used for security—they can return different data based on the user executing the UDF. For instance, the following UDF returns the hire date only if executed by the database owner:
CREATE FUNCTION dbo.udf_get_employee (@p1 INT)
RETURNS TABLE
AS
RETURN
(
SELECT emp_id, fname, lname,
hire_date =
CASE WHEN user_name() = 'dbo' THEN CAST(hire_date AS VARCHAR(12)) ELSE 'n/a' END
FROM
employee
WHERE job_id = @p1
)This UDF can be executed as follows:
SELECT * FROM dbo.udf_get_employee(13)If executed by the database owner, the output is the following:
emp_id fname lname hire_date
--------- -------------------- ------------------------------ ------------
PMA42628M Paolo Accorti Aug 27 1992
TPO55093M Timothy O'Rourke Jun 19 1988
CGS88322F Carine Schmitt Jul 7 1992If executed by any other user, the output will contain "n/a" in the "hire_date" column.
Multi-Statement Functions: Alternatives to Stored Procedures
Multi-statement functions allow you to perform additional logic, such as variable declaration, populating of table variables, and updating values within it, looping through records and more. Multi-statement functions are a great way to rewrite a stored procedure if the following conditions are true:
The output of the stored procedure can be used by other routines.
Stored procedures don't make any data changes in the permanent tables.
The only purpose of the stored procedure is to create a result set that might have to be manipulated prior to returning it to the user.
Stored procedures do not perform any tasks prohibited inside the UDFs (see the next section for details
|
No responses found. Be the first to respond and make money from revenue sharing program.
|