Scalar User-Defined Functions

Scalar user-defined functions will often return a value based on one or more arguments passed to them. For example, you can develop scalar functions to convert from one unit of measurement to another, such as Fahrenheit to centigrade, degrees to radians, feet to meters, and dollars to pounds. Another type of application for user-defined functions is the computation of complicated formulas. This type application is appropriate in situations where all the units within an organization must identically perform a mission-critical calculation.

You can launch the creation of a user-defined function by selecting Queries in the Objects bar of the Database window and clicking New. From the New Query dialog box, you can choose Create Text Scalar Function, Design In-Line Function, or Create Text Table-Valued Function. These correspond to the first, second, and third types of user-defined functions.

If you choose Create Text Scalar Function, Access responds by presenting a template such as the one that follows for constructing a scalar user-defined function. Notice that this template has a space for inserting arguments in parentheses after the function name. In addition, the template includes a mandatory RETURNS statement. Use this statement to specify the data type of the scalar value returned by a function. An AS keyword marks the beginning of the user-defined code. All T-SQL statements for the function must reside between BEGIN and END statements. The T-SQL code within these statements must include at least one RETURN statement. The argument for this statement determines the return value of the function.

CREATE FUNCTION "Function1"
    (
    /*
    @parameter1 datatype = default value,
    @parameter2 datatype
    */
    )
RETURNS /* datatype */
AS
    BEGIN
        /* sql statement ... */
    RETURN /* value */
    END 

The next T-SQL script is a very simple scalar function. Notice that the function starts with an ALTER FUNCTION statement. Access automatically converts a CREATE FUNCTION statement into an ALTER FUNCTION statement after you initially save the function from one of the Access user-defined function templates. The following function returns a single value. This value derives from the built-in AVG function applied to the Freight column values of the Orders table. Although the source for the function is a table, it returns a scalar value—the average Freight value from the Orders table. This value returns with a Money data type.

ALTER FUNCTION dbo.AVG_Freight_overall()
RETURNS money
AS
BEGIN
    RETURN (SELECT AVG(Freight) FROM Orders)
END

As long as the values in the Freight column do not change, this function will always return the identical value. Users cannot directly alter the function's return value based on arguments they submit to the function. However, functions often incorporate additional functions that determine what their output will be. For example, you can define a function that returns the average freight for just one year—instead of all the years combined as the preceding sample does. The following function illustrates the syntax for this kind of user-defined function. Notice the specification of the parameter to accept an argument in the parentheses after the function name. An argument declaration must always include a name and a data type specification. The SELECT statement for this function includes a WHERE clause that uses the argument to restrict the range of values over which the AVG function computes the average freight.

ALTER FUNCTION dbo.Avg_Freight_in_@year (@year int)
RETURNS money
AS
BEGIN
    RETURN (
        SELECT AVG(Freight) 
        FROM Orders 
        WHERE DATEPART(yyyy,OrderDate) = @year
        )
END

You can also base scalar user-defined functions exclusively on scalar inputs—without relying on a tabular row source. The next sample computes the percentage of change between two positive numbers. Both input values have a Float data type. The return value is a Decimal data type with a scale of 6 and a precision of 3. This function illustrates the use of a local variable named @change with a Float data type. SQL Server local variables in functions and stored procedures have scope (or visibility) only within the function or procedure in which you define them. The local variable saves the difference between the two arguments for use in the percent change expression, which is the argument for the RETURN statement. This expression returns a percent with a value of 0.1 as 10 percent because it multiplies the percent change by 100.

ALTER FUNCTION dbo.Percent_change (@firstnumber float, 
    @secondnumber float)
RETURNS decimal(6,3)
AS
BEGIN
DECLARE @change float
    SET @change = @secondnumber-@firstnumber
    RETURN @change/@firstnumber*100
END

You can invoke user-defined functions from other SQL Server database objects, such as stored procedures, or from VBA procedures. The following stored procedure invokes the preceding two user-defined functions to compute the average freight in each of two different years as well as the percent change between those two years. Because the functions return numeric data types, it's necessary to convert them for use as the argument of the RAISERROR statement. The CONVERT functions in the stored procedure require a character data type specification for the @year1 and @year2 parameters. This statement, which concludes the stored procedure, presents a message box when you run the stored procedure from the Database window. If you run the stored procedure from a VBA procedure, it passes back its computed string as an error message to the VBA procedure.

ALTER PROCEDURE ComputePercentChangeBetweenYears
    @year1 char(4),
    @year2 char(4)
AS
DECLARE @str1 as varchar(100)
   
--Compute a string expression based on the Avg_Freight_in_@year 
--and Percent_change functions
SET @str1 = 
    'Freight in ' + @year1 + ' = $' 
        + CONVERT(varchar(7),dbo.Avg_Freight_in_@year(@year1)) 
        + CHAR(10) + 
    'Freight in ' + @year2 + ' = $' 
        + CONVERT(varchar(7),dbo.Avg_Freight_in_@year(@year2)) 
        + CHAR(10) + 
    'Percent change = ' 
        + CONVERT(varchar(7),
        dbo.Percent_change(dbo.Avg_Freight_in_@year(@year1), 
        dbo.Avg_Freight_in_@year(@year2))) + '%'
   
--Return the string from the stored procedure
RAISERROR 50000 @str1

If you invoke this stored procedure from the Database window, it sequentially presents a pair of prompts for its two arguments. Insert 1996, 1997, or 1998, because those are the only years for which the Orders table has data. Figure 11-24 shows the format of the message box that the procedure generates when it concludes. This message box shows the average freight in 1996 and 1997, along with the percent change from 1996 to 1997.

Figure 11.24: A message box generated by the ComputePercentChangeBetweenYears stored procedure when it's run from the Database window.

When you run the same procedure from a VBA procedure, no message box appears. In fact, the procedure returns a run-time error. The Description property of the Err object for the error contains the same content shown in Figure 11-24. Therefore, you can print the Err object's Description property to the Immediate window to display this content. The following pair of procedures shows one approach to accomplishing this. The first procedure specifies the year parameters that serve as arguments for the stored procedure. It passes these values to the second procedure, which constructs a SQL string (str1) for a Connection object to execute. Before invoking the Connection object's Execute method, the procedure opens an error trap that passes control to the next line of code. Because the stored procedure in the SQL string passes back its content through an error message, this error trap—which prints the Err object's Description property—retrieves the message. Without the error trap, an error box would still appear with the content but the procedure would lose control of the session.

Sub CallVBAPercentChange() 
Dim str1 As String
Dim str2 As String
   
'Assign string values of 1996, 1997, or 1998
'to str1 and str2
str1 = "1996"
str2 = "1998"
   
'Call procedure to invoke stored procedure
'that returns values from user-defined functions
VBAPercentChange str1, str2
   
End Sub
   
Sub VBAPercentChange(year1 As String, year2 As String) 
Dim str1 As String
Dim cnn1 As ADODB.Connection
   
'Point cnn1 at current project's database connection
Set cnn1 = CurrentProject.Connection
   
'Invoke stored procedure, pass arguments, and print return
str1 = "Exec ComputePercentChangeBetweenYears " & year1 & _
    ", " & year2
On Error Resume Next
cnn1.Execute str1
Debug.Print Err.Description
   
End Sub

In-Line User-Defined Functions

In-line functions return a table of values in the result set for a single SELECT statement. In addition, in-line functions have a simplified syntax. They do not require BEGIN and END statements, and they must have only one RETURN statement for their single SELECT statement.

In-line functions can behave like views. In addition, you can assign parameters to their WHERE clauses—a feature that views lack. Stored procedures do possess this functionality, but you cannot use a stored procedure as a FROM clause argument like you can with an in-line function procedure.

Because an in-line function depends on a single SELECT statement, Access 2003 offers a graphical design tool that is very similar to the visual designer for views. You can create an in-line function by selecting Queries in the Objects bar on the Database window and clicking New. Then you double-click Design In-Line Function. This opens the visual designer for in-line functions that appears in Figure 11-25. Notice that you can use tables, views, and other table-returning functions as sources for in-line functions. You can build the query similarly to the way you build a view, but you can specify criteria with parameters (as with stored procedures). In addition, the View drop-down menu on the toolbar offers a SQL View option as well as a SQL pane. With the SQL pane, you can see the T-SQL syntax for the SELECT statement underlying an in-line function's graphical view. With the SQL View window, you can see the full ALTER FUNCTION statement defining the in-line function.

Figure 11.25: The visual designer for in-line functions lets you base a function on tables, views, or other table-returning functions.

Figure 11-26 shows the visual designer for an in-line function that returns all rows from the Orders table with a Freight column value greater than the average of the Freight values for all rows in that table. The function also sorts its result set in ascending order. The T-SQL code appears in the bottom SQL pane. Notice that it follows the view syntax for defining a sort—in other words, the SELECT statement must include a TOP clause. This syntax also shows the code for nesting one user-defined function within another. This is because the average Freight column value across all rows in the Orders table is based on the AVG_Freight_overall function. Notice that this function's identifier includes a user qualifier that points to the dbo user. Function identifiers must be unique within their respective owners. A pair of parentheses follows the function name even though the function takes no arguments. As mentioned previously, these parentheses are mandatory.

Figure 11.26: A visual designer view of an in-line function that has another user-defined function nested within it.

The following code listing shows the SQL View window of the in-line function whose design appears in Figure 11-26. The SQL View window exposes the ALTER FUNCTION, RETURNS, and RETURN statements. These statements serve as a shell and a conduit for returning the table defined by the function's SELECT statement. The ALTER FUNCTION is the outside wrapper for the T-SQL code. The RETURNS statement specifies that the function returns a TABLE. The RETURN statement is a wrapper for the SELECT statement that specifies the result set for the function.

ALTER FUNCTION dbo.AboveAverageOrders
()
RETURNS TABLE
AS
RETURN ( SELECT TOP 100 PERCENT *
FROM     dbo.Orders
WHERE    (Freight > dbo.AVG_Freight_overall())
ORDER BY Freight )

The following VBA procedure offers one approach to extracting values from the result set for an in-line function. The trick here is to use a T-SQL statement that returns all columns from the result set for the function. This statement becomes the source for the Open method of a Recordset object. By setting the recordset's MaxRecord property to 5, we return a small subset of the entire result set to loop through and list in the Immediate window. If you want to retrieve the full result set, omit the MaxRecord property setting. Be aware that if you do so, you might need to change the data type setting for byt1 to a data type that can accommodate a larger limit.

Sub ReturnResultsSetFromInlineFunction()
Dim rst1 As ADODB.Recordset
Dim byt1 As Byte
   
'Get a subset of the results set from an in-line function
Set rst1 = New ADODB.Recordset
rst1.MaxRecords = 5
rst1.Open "SELECT * FROM dbo.AboveAverageOrders()", _
    CurrentProject.Connection
   
'Print a subset of results set record from the in-line function
Do Until rst1.EOF
    Debug.Print "Results for record " & (byt1 + 1)
    For Each fld1 In rst1.Fields
        Debug.Print "   " & fld1.Name & " = " & rst1.Fields(fld1.Name)
    Next fld1
    rst1.MoveNext
    byt1 = byt1 + 1
    Debug.Print
Loop
   
End Sub

Multistatement User-Defined Functions

User-defined functions returning tables can also contain multiple statements—instead of just the single SELECT statement in a typical user-defined function. Books Online calls this type of user-defined function a multistatement table-valued function. This long description accurately reflects the structure of this kind of user-defined function. This kind of function lets you use many different types of T-SQL statements to define its result set. At a minimum, using this function will require you to specify the data types for columns and the record sources for the result set. Additionally, you will need to include one or more INSERT statements for copying values from one or more record sources into the return set.