Reviewing Advanced UDF Topics

Although creating a user-defined function is a fairly simple process, using them is a bit more interesting. Of course, to repeat myself, when you decide to create a UDF for your database, you need to do some analysis on what you really want the function to do and what type of information the function will return when it is executed. In addition, when you create user-defined functions, the performance of the function is very important because it will be used by many programs to access information from the database.

Finally, you saw earlier in this lesson that errors must be accounted for in a UDF just as in triggers and stored procedures. However, the server responds to errors in a UDF in a slightly different way. In this section, you will look at some performance recommendations, what you need to know when accessing a UDF from a view, and error checking.

Rewriting Stored Procedures as Functions

On Days 15, "Writing and Executing Stored Procedures," and 16, "Optimizing Stored Procedures," you learned what stored procedures are, how to create them, and how to use them in your SQL programming. However, one of the places you cannot use a stored procedure is from within a query. If you want to invoke a stored procedure directly from a query, you should to repackage the code in the stored procedure as a user-defined function. In general, if the stored procedure returns a single result set, you can define a table-valued function; if it returns a single value, you can define a scalar function.

To see whether a stored procedure can be converted to a user-defined function, compare it to the following criteria. If the stored procedure meets all the following requirements, it is a good candidate to be converted.

• A single SELECT statement is used in a stored procedure, rather than a view, because it requires parameters, so a scalar function can be used.

• The stored procedure does not perform any update operations.

• Dynamic EXECUTE statements are not needed.

• Only one result set is returned.

• The primary purpose is to build a temporary result set that is loaded into a temporary table, which is then used in a SELECT statement.

UDF Performance and Recommendations

To help you produce good quality user-defined functions, this section provides some recommendations and tips for working with them.

Using Scalar Functions

Scalar functions are useful for situations in which you need to do the same math calculations in more than one place in the SQL code. For example, if calculating interest based on percent rate, principal, and years is done throughout your application, it can be coded as a user-defined function, as shown in the following example:

Create function calcInt (@prin int,
                         @rate numeric(10,5),
                         @yrs int)

Returns int
As
Begin
     Declare @interest int
     Set @interest = @prin * @rate * @yrs / 100
     Return @interest
End
							

Using System Functions in UDFs

System functions can be used as building blocks for a user-defined function. For example, if you need to calculate the quadrupled value of a number, use the SQUARE system function to arrive at the value instead of writing the entire function from scratch.

Nesting Functions to Simplify a Complex Function

If you are trying to create a very complex function, you can break down the complexity of the process into several smaller functions that can then be used together in a larger function. The advantage of breaking complex functions into smaller functions is that this code can be reused in more places in the application.

For example, suppose that you need to calculate the area of a plot of land and the input can be in either meters or feet, but the area must always be displayed in square feet. Instead of writing one function that does all the work, you can break up the task into two functions:

• cvt_meters_feet() does the conversion from meters to feet.

• calc_area_feet() calculates the area in feet.

This way, you can use the cvt_meters_feet() function at other places in the code.

Create Function cvt_meters_feet (@inVal numeric(10,3))
Returns numeric(10,3)
As
Begin
    Declare @ret_feet numeric(10,3)
    Set @ret_feet = @inVal * 3.281
    Return(@ret_feet)
End

In the second function, calc_area_feet(), you can use the cvt_meters_feet() function as shown here:

Create Function calc_area_feet (@inLength numeric(10,3),
                                @inWidth numeric(10,3),
                                @inUnit char(1))
Returns numeric(10,3)
As
Begin
    Declare @area numeric(10,3)
    If @inUnit = 'm'
    Begin
        Set @inLength = dbo.cvt_meters_feet(@inLength)
        Set @inWidth = dbo.cvt_meters_feet(@inWidth)
    End
    Set @area = @inLength * @inWidth
    Return @area
End

Now, you can use the final function to calculate either meters or feet. The following shows how to execute this function for both types of measurements:

Select dbo.calc_area_feet (95,45.5, 'M') As 'Area in Meters'
Select dbo.calc_area_feet (95,45.5, 'F') As 'Area in Feet'

The output from these statements would look like this:

Area in Meters
--------------
46531.700

(1 row(s) affected)

Area in Feet
------------
4322.500

(1 row(s) affected)
							

Considering the Effects of Changes to the Schema

If SELECT * FROM <table> is used in a function, effects of changes to the structure of the database after the creation of the function should be taken into account. If the function is not created with the SCHEMA_BINDING option, changes to the structure are not reflected in the result.

For example, if a new column is added to the table after the function was created and the function is not SCHEMA bound, the new column will not show up in the result set. If a column is removed after creation of the function and the function is not SCHEMA bound, a NULL value will show up in the result set for the deleted column.

Using Functions Instead of Views

Any user-defined function that returns a table can be a very powerful replacement to a view. Views are limited to a single SELECT statement. However, a user-defined function can contain any number of statements that enable you to create much more powerful logic than is possible in a view. If your SQL application requires a complex set of data, you used to be required to create two or more views that you would then join together to get the appropriate data. Using user-defined functions, you could build a temporary table in the function code that can include many tables and, more importantly, complex calculations and data manipulation all rolled up together.

As an example of how using a UDF instead of a view can provide you with extended capabilities, let's take a look at the following view:

Create View vw_CustomerOrdersSAVEA As
select *
from orders
inner join [order details]
on orders.orderid = [order details].orderid
Where Orders.customerID = 'SAVEA'

By using a user-defined function, you can create a more general version of this view by replacing WHERE orders.customerId = 'SAVEA' with WHERE orders.customerId = @custId and letting the user supply the customer if he wants to do so. Views, on the other hand, do not support parameters in the WHERE condition. The following is the same query rewritten as a user-defined function:

Create Function fn_CustomerOrders (@CustId varchar(10))
Returns Table
As
Return (
         Select *
         From orders
         Inner join [order details]
         On orders.orderid = [order details].orderid
         Where Orders.customerID = @CustId
        )
						

Error Trapping and Functions

Any T-SQL statements that would cause a statement to be cancelled with execution continuing on to the next statement in the script (such as triggers and stored procedures) are treated a little differently when they occur inside a user-defined function. In a UDF, such errors will cause the execution of the function to stop. This will in turn cause the statement that invoked the function to be cancelled.