Applying User-Defined Functions

You convert commonly used formulas into scalar user-defined functions. In this case, the function's compiled query plan remains in memory, as does any built-in function.

You can call user-defined functions from inside other user-defined functions, but only up to 32 levels, and this limit applies to the total of stored procedures, triggers, and scalar or table-valued user-defined functions you use.

A good approach would be to create user-defined functions in a short number of layers, so the limit for nesting levels will never be surpassed. This contributes to the clarity of your database design as well.

Be aware that modifying underlying objects could affect the result of a user-defining function, unless you create the function with the SCHEMABINDING option.

This is still a new feature for Transact-SQL programmers, but client- application programmers will find user-defined functions very close to their normal programming methods.

Converting Stored Procedures into User-Defined Functions

If the only reason for a stored procedure is to supply an output parameter, you can create a scalar user-defined function instead. In this way, you can use this function in a more natural way than a stored procedure. Listing 10.24 shows an example of converting the fn_FV function into the sp_FV stored procedure and how to call them.

Code Listing 10.24. Comparing a Stored Procedure with a Scalar User-Defined Function

						
USE Northwind
GO

-- sp_fv with the same functionality
-- as the fn_fv function

CREATE PROCEDURE sp_fv
@rate float, @nper int, @pmt money,
@pv money = 0.0, @type bit = 0,
@FV money output
AS

IF @rate = 0
SET @fv = @pv + @pmt * @nper
ELSE
SET @fv = @pv * POWER(1 + @rate, @nper) +
@pmt * (((POWER(1 + @rate, @nper + @type) - 1) / @rate) - @type)

SET @fv = -@fv
GO

-- Call the sp_fv stored procedure

DECLARE @fv money

EXECUTE sp_fv 0.10, 24, 1000, 10000, 0, @fv OUTPUT

SELECT @fv 'From sp_fv'
GO

-- Call the sp_fv stored procedure

SELECT dbo.fn_fv(0.10, 24, 1000, 10000, 0) as 'From fn_fv'
GO

From sp_fv
---------------------
-186994.6535

From fn_fv
---------------------
-186994.6535

If a stored procedure returns a single read-only result set, you can convert it into a table-valued function with a similar code, and you can use the function in the FROM clause of any DML statement, providing a better programming flexibility. Listing 10.25 shows an example of a stored procedure with the same functionality as the tv_TopTenOrders and how to call them.

If you have a stored procedure that provides read/write access to a table through a client library, you can convert this procedure into an inline user-defined function.

Code Listing 10.25. Comparing Stored Procedures and Table-Valued User-Defined Functions

						
USE Northwind
GO

CREATE PROCEDURE sp_TopTenOrders
AS

DECLARE @list TABLE
(OrderID int,
CustomerID nchar(5),
OrderDate datetime,
TotalValue money)

INSERT @List
SELECT O.OrderID, CustomerID, OrderDate, TotalValue
FROM Orders O
JOIN (
SELECT OrderID, SUM(dbo.TotalPrice(Quantity, UnitPrice, Discount))
AS TotalValue
FROM [Order Details]
GROUP BY OrderID) AS OD
ON O.OrderID = OD.OrderID

SELECT TOP 10 WITH TIES
OrderID, CustomerID, OrderDate, TotalValue
FROM @List
ORDER BY TotalValue DESC
GO

EXECUTE sp_TopTenOrders
GO

SELECT *
FROM tv_TopTenOrders()
GO

OrderID     CustomerID OrderDate                     TotalValue
----------- ---------- ----------------------------- ---------------------
10865       QUICK      1998-02-02 00:00:00.000       16387.5000
10981       HANAR      1998-03-27 00:00:00.000       15810.0000
11030       SAVEA      1998-04-17 00:00:00.000       12615.0500
10889       RATTC      1998-02-16 00:00:00.000       11380.0000
10417       SIMOB      1997-01-16 00:00:00.000       11188.4000
10817       KOENE      1998-01-06 00:00:00.000       10952.8450
10897       HUNGO      1998-02-19 00:00:00.000       10835.2400
10479       RATTC      1997-03-19 00:00:00.000       10495.6000
10540       QUICK      1997-05-19 00:00:00.000       10191.7000
10691       QUICK      1997-10-03 00:00:00.000       10164.8000
OrderID     CustomerID OrderDate                     TotalValue
----------- ---------- ----------------------------- ---------------------
10865       QUICK      1998-02-02 00:00:00.000       16387.5000
10981       HANAR      1998-03-27 00:00:00.000       15810.0000
11030       SAVEA      1998-04-17 00:00:00.000       12615.0500
10889       RATTC      1998-02-16 00:00:00.000       11380.0000
10417       SIMOB      1997-01-16 00:00:00.000       11188.4000
10817       KOENE      1998-01-06 00:00:00.000       10952.8450
10897       HUNGO      1998-02-19 00:00:00.000       10835.2400
10479       RATTC      1997-03-19 00:00:00.000       10495.6000
10540       QUICK      1997-05-19 00:00:00.000       10191.7000
10691       QUICK      1997-10-03 00:00:00.000       10164.8000

Converting Views into User-Defined Functions

You can convert views into inline user-defined functions very easily, but in this case, the only benefit you will get is the possibility of having parameters. However, if you use a view to read data only, you will benefit from converting this view into a table-valued function because it will be optimized and compiled on the first execution, providing performance gains over a view.

Listing 10.26 shows the fv_TopTenOrders converted into a view, and how you call the view and the user-defined function. The output is the same as the one for Listing 10.25.

Code Listing 10.26. Comparing Views and Table-Valued User-Defined Functions

						
USE Northwind
GO

CREATE VIEW vw_TopTenOrders
AS

SELECT TOP 10 WITH TIES
O.OrderID, CustomerID, OrderDate, TotalValue
FROM Orders O
JOIN (
SELECT OrderID, SUM(dbo.TotalPrice(Quantity, UnitPrice, Discount))
AS TotalValue
FROM [Order Details]
GROUP BY OrderID) AS OD
ON O.OrderID = OD.OrderID
ORDER BY TotalValue DESC

GO

SELECT *
FROM tv_TopTenOrders()
GO

SELECT *
FROM vw_TopTenOrders
GO

Using User-Defined Functions in Constraints

You can use a scalar user-defined function anywhere an expression is allowed, and that includes

• DEFAULT constraints

• CHECK constraints

• DEFAULT objects

• RULE objects

• A PRIMARY KEY constraint defined in a computed column using a user-defined function, as long as the returned values are unique

• A UNIQUE constraint defined in a computed column with a user-defined function, as long as the returned values are unique

Therefore, it is possible to access values from other tables from inside a constraint, as long as the constraint uses a user-defined function that searches for external data to produce its result.

The only place where you can use a table-valued user-defined function or an inline user-defined function is as a subquery in a CHECK constraint but, unfortunately, CHECK constraints do not support subqueries.

What's Next?

This chapter covered the creation and use of user-defined functions—an exciting new feature that provides extra programmability to the Transact-SQL language. The more you practice with user-defined functions, the more you will wonder how you could have survived without them before SQL Server 2000 offered this feature.

Chapter 11 teaches you how to write complex queries, and in some cases, using user-defined functions that could solve similar situations with less complexity.

In Chapter 12, you learn how to work with result sets row by row, using cursors. You can use cursors inside user-defined functions to achieve complex operations that are impossible using rowset-oriented programming.