User-Defined Functions

Just as I've showed stored procedure examples (using sp_help and so on) in earlier chapters, I've also used functions. You've seen parameterized functions such as CONVERT, RTRIM, and ISNULL as well as parameterless functions such as @@SPID. SQL Server 2000 lets you create functions of your own. However, whereas user-defined procedures are managed similarly to system stored procedures, the functions you create are managed very differently from the supplied functions. As I've mentioned, the ANSI SCHEMA VIEW called ROUTINES allows you to see all the procedures and functions in a database. This view shows system-defined stored procedures but not built-in functions such as the ones you've seen in earlier chapters. You should think of the built-in functions as almost like built-in commands in the SQL language; they are not listed in any system table, and there is no way for you to see how they are defined.

Table Variables

To make full use of user-defined functions, it's useful to understand a special type of variable that SQL Server 2000 provides. You can declare a local variable as a table or use a table value as the result set of a user-defined function. You can think of table as a special kind of datatype. Note that you cannot use table variables as stored procedure or function parameters, nor can a column in a table be of type table.

Like the other kinds of local variables I told you about in Chapter 10, table variables have a well-defined scope, which is limited to the procedure, function, or batch in which they are declared. Here's a simple example:

 DECLARE @pricelist TABLE(tid varchar(6), price money) INSERT @pricelist SELECT title_id, price FROM titles SELECT * FROM @pricelist 

The definition of a table variable looks almost like the definition of a normal table, except that you use the word DECLARE instead of CREATE, and the name of the table variable comes before the word TABLE.

The definition of a table variable can include:

• A column list defining the datatypes for each column and specifying the NULL or NOT NULL property
• PRIMARY KEY, UNIQUE, CHECK, or DEFAULT constraints

The definition of a table variable cannot include:

• Foreign key references to other tables
• Columns referenced by a FOREIGN KEY constraint in another table

Within their scope, table variables can be treated like any other table. All data manipulation statements (SELECT, INSERT, UPDATE, and DELETE) can be performed on the data in a table variable, with two exceptions:

• You cannot use SELECT INTO to add data to a table variable. This is because SELECT INTO creates a table, and table variable must be created using DECLARE. For example, you cannot do the following:

   SELECT select_list INTO table_variable statements 

• You cannot directly INSERT the result set of a stored procedure into a table variable. For example, you cannot do the following:

   INSERT INTO table_variable EXEC stored_procedure 

In addition, you need to be aware of the following facts:

• A table variable is not part of a transaction. A ROLLBACK TRANSACTION command will not affect data added to a table variable. (The same is true of any other nontable variables.)
• You cannot use the CREATE INDEX command to build an index on a table variable.

Scalar-Valued Functions

You can use a scalar-valued function anywhere that your Transact-SQL command is expecting a value. It can take up to 1024 input parameters but no output parameters. The function can return a value of any datatype except rowversion (or timestamp), cursor, or table.

Unlike a stored procedure, a function must include the RETURN statement. (In stored procedures, the RETURN is optional.) You can declare a scalar local variable to hold the return value of the function and then use this variable with the RETURN statement, or the RETURN statement can include the computation of the return value. The following two function definitions are equivalent; both will return the average price for books in the titles table for which the type is the same as the type specified as the input parameter:

 CREATE FUNCTION AveragePrice(@booktype varchar(12)) RETURNS money AS BEGIN DECLARE @avg money SELECT @avg = avg(price) FROM titles WHERE type = @booktype RETURN @avg END CREATE FUNCTION AveragePrice2(@booktype varchar(12)) RETURNS money AS BEGIN RETURN ( SELECT avg(price) FROM titles WHERE type = @booktype) END 

Invoking a Scalar Function

To invoke a user-defined scalar function, you must specify the owner name. The following query will return an error:

 SELECT title_id, price FROM titles WHERE price > AveragePrice('business') AND type = 'business' RESULT: Server: Msg 195, Level 15, State 10, Line 3 'AveragePrice' is not a recognized function name. 

Here's the correct invocation:

 SELECT title_id, price FROM titles WHERE price > dbo.AveragePrice('business') AND type = 'business' RESULT: title_id price -------- --------------------- BU1032 19.9900 BU7832 19.9900 

You can invoke user-defined scalar functions by simply SELECTing their value:

 SELECT dbo.AveragePrice ('business') 

You can also use the keyword EXECUTE to invoke a user-defined function as if it were a stored procedure. You might want to do this to assign the return value to a variable. In this case, though, the syntax is different. You must not specify the parameter list in parentheses, as in the examples above. Instead, you should just list them after the name of the function:

 DECLARE @avg money EXEC @avg = dbo.AveragePrice 'business' SELECT @avg 

Additional Restrictions

The SQL statements inside your scalar-valued functions cannot include any nondeterministic system functions. I talked about determinism in Chapter 8 when I discussed the requirements for creating Indexed Views and indexes on computed columns.

For example, suppose I want a function that will format today's date using any separator that I supply. I might try to write the function like this:

 CREATE FUNCTION MyDateFormat (@Separator char(1)='-') RETURNS nchar(20) AS BEGIN DECLARE @indate datetime SELECT @indate = GETDATE() RETURN CONVERT(nvarchar(20), DATEPART(dd, @indate)) + @Separator + CONVERT(nvarchar(20), DATEPART(mm, @indate)) + @Separator + CONVERT(nvarchar(20), DATEPART(yy, @indate)) END 

However, because GETDATE is nondeterministic, I get this error:

 Server: Msg 443, Level 16, State 1, Procedure MyDateFormat, Line 7 Invalid use of 'getdate' within a function. 

I can change the function definition to accept a datetime value as an input parameter and then call the function with GETDATE as an argument. Not only does this allow me to accomplish my goal, but also it makes my function much more versatile. Here's the function:

 CREATE FUNCTION MyDateFormat (@indate datetime, @Separator char(1)='-') RETURNS nchar(20) AS BEGIN RETURN CONVERT(nvarchar(20), DATEPART(dd, @indate)) + @Separator + CONVERT(nvarchar(20), DATEPART(mm, @indate)) + @Separator + CONVERT(nvarchar(20), DATEPART(yy, @indate)) END 

And here's an example of its invocation:

 SELECT dbo.MyDateFormat(GETDATE(), '*') RESULT: -------------------- 18*7*2000 

As a final example, I'll rewrite the factorial routine as a function. As you've seen, we can go only one value higher using the iterative solution instead of the recursive solution. Since functions work very nicely recursively, I'll rewrite it as a recursive function. The function will call itself by multiplying the input parameter by the factorial of the number one less than the parameter. So 10 is computed by finding the factorial of 9 and multiplying by 10. To avoid overflow problems, the function simply returns a 0 for the result of any input value that is out of range. Here's the function:

 CREATE FUNCTION fn_factorial (@param decimal(38, 0) ) RETURNS decimal(38, 0) AS BEGIN IF (@param < 0 OR @param > 32) RETURN (0) RETURN (CASE WHEN @param > 1 THEN @param * dbo.fn_factorial(@param - 1) ELSE 1 END) END 

Unlike the factorial procedure, which lists all the factorial values up to and including the factorial of the input parameter, the function fn_factorial simply returns the value that is the factorial for the argument. Remember that you must always specify a two-part name when you call a user-defined scalar function:

 SELECT factorial = dbo.fn_factorial(10) RESULT: factorial ---------------------------------------- 3628800 

Table-Valued Functions

Table-valued functions return a rowset. You can invoke them in the FROM clause of a SELECT statement, just as you would a view. In fact, you can think of a table-valued function as if it were a parameterized (or parameterizable) view. A table-valued function is indicated in its definition using the word TABLE in the RETURNS clause. There are two ways to write table-valued function: as inline functions or as multistatement functions. This difference is relevant only to the way the function is written; all table-valued functions are invoked in the same way. You'll also see in Chapter 15 that there is a difference in the way that the query plans for inline and multistatement functions are cached.

Inline Functions

If the RETURNS clause specifies TABLE with no additional table definition information, the function is an inline function and there should be a single SELECT statement as the body of the function. Here's a function that will return the names and quantities of all the books sold by a particular store for which the store ID is passed as an input parameter:

 CREATE FUNCTION SalesByStore(@storid varchar(30)) RETURNS TABLE AS RETURN (SELECT title, qty FROM sales s, titles t WHERE s.stor_id = @storid AND t.title_id = s.title_id) 

We can execute this function by invoking it in a FROM clause:

 SELECT * FROM SalesByStore ('8042') RESULT: title qty ------------------------------------------------------ ------ The Gourmet Microwave 15 The Busy Executive's Database Guide 10 Cooking with Computers: Surreptitious Balance Sheets 25 But Is It User Friendly? 30 

Note that for a table-valued function, you don't have to include the owner name.

Multistatement Table-Valued Functions

If the RETURNS clause specifies that it's returning a TABLE and specifying a table variable name, the function is a multistatement table-valued function. The RETURNS clause also lists the columns and datatypes for the table. The body of the function populates the table using INSERT and possibly UPDATE statements. When the RETURN is executed, it simply returns the table variable specified earlier in the RETURNS clause.

Here's an example of the previous inline function rewritten as a multistatement function:

 CREATE FUNCTION SalesByStore_MS(@storid varchar(30)) RETURNS @sales TABLE(title varchar(80), qty int) AS BEGIN INSERT @sales SELECT title, qty FROM sales s, titles t WHERE s.stor_id = @storid AND t.title_id = s.title_id RETURN END 

The function is invoked exactly as the inline table-valued function is invoked.

The following statements are the only ones allowed in a multistatement table-valued function:

• Assignment statements
• Control-of-flow statements
• DECLARE statements that define data variables and cursors that are local to the function
• SELECT statements containing select lists with expressions that assign values to variables that are local to the function
• Cursor operations referencing local cursors that are declared, opened, closed, and deallocated in the function. Only FETCH statements that assign values to local variables using the INTO clause are allowed; FETCH statements that return data to the client are not allowed. (For more details on cursors, see Chapter 13.)
• INSERT, UPDATE, and DELETE statements that modify table variables that are local to the function

Also, like for scalar functions, table-valued functions cannot contain any built-in nondeterministic functions.

Side Effects

Basically, the statements that aren't allowed are ones that return data other than the function's return value and the ones that product side effects. A side effect is a change to some persisted state that is not local to the function. Invoking a function should not change your database in any way; it should only return a value (scalar or table-valued) to the client. Thus, the following are not allowed:

• Updates to tables
• Global cursor statements
• Creation of objects
• Transaction control statements

System Table-Valued Functions

SQL Server 2000 includes a set of systemwide table-valued functions that can be invoked from any database. Normally, the table-valued functions you create will be objects in one particular database, and to invoke them from another database, you must specify the database name. System table-valued functions do not have this restriction. However, to invoke one of these functions, you must use a special syntax and precede the function name with ::. In fact, you cannot specify an owner or database name; you can only use this special syntax.

Here are the documented system table-valued functions:

 fn_helpcollations fn_listextendedproperty fn_servershareddrives fn_trace_geteventinfo fn_trace_getfilterinfo fn_trace_getinfo fn_trace_gettable fn_virtualfilestats fn_virtualservernodes 

The function fn_virtualfilestats returns I/O statistics for a database file and requires a database ID and a file ID as parameters. The following example calls this function for the data file of the pubs database. We'll discuss the meaning of some of the return values in Chapter 17.

 SELECT * FROM ::fn_virtualfilestats(5, 1) RESULT: DbId FileId TimeStamp NumberReads NumberWrites BytesRead ------ ------ ----------- ------------ ------------ ------------ 5 1 34138899 115 9 942080 BytesWritten IoStallMS ------------- ----------------- 90112 4585 

Managing User-Defined Functions

Just like a view, a function can be created WITH SCHEMABINDING. This option means that any object referenced by the function cannot be dropped or altered. Any attempt to alter such a referenced object will result in an error. Let's use the inline table-value function I created above and re-create it WITH SCHEMABINDING. Note that we can use ALTER FUNCTION to change the function's definition without dropping and re-creating it. However, we cannot use ALTER to change a table-valued function to a scalar function or to change an inline function to a multistatement function.

 ALTER FUNCTION SalesByStore(@storid varchar(30)) RETURNS TABLE WITH SCHEMABINDING AS RETURN (SELECT title, qty FROM dbo.sales s, dbo.titles t WHERE s.stor_id = @storid AND t.title_id = s.title_id) 

A function can be schema-bound only if the following conditions are true:

• The user-defined functions and views referenced by the function are also schema-bound.
• All objects referenced by the function are referred to by a two-part name.
• The function is in the same database as all the referenced objects.
• Any user referencing the function has REFERENCES permission on all the database objects that the function references.

SQL Server keeps track of objects that are dependent on other objects in the sysdepends table. The data in the table looks very cryptic—it's all numbers. To get meaningful information from the sysdepends table, you have to get the names of the objects stored in the id column, which are the dependent objects, and the names of the objects stored in the depid column, which are the referenced objects.

For example, in my SalesByStore function above, the function itself is the dependent object, and the objects it depends on are the titles and sales tables. The depnumber column refers to a column in the referenced object that the dependent object depends on. My SalesByStore functions depend on the qty, stor_id, and title_id columns from sales and the title_id and title columns from titles. Finally, the column deptype indicates whether the dependency is schema-bound—that is, whether a change to the referenced column should be prevented. By accessing the syscolumns and sysdepends tables and using the OBJECT_NAME function to extract the name from sysobjects, we can see what columns are schema-bound in the SalesByStore functions:

 SELECT obj_name = SUBSTRING(OBJECT_NAME(d.id), 1, 20), dep_obj = SUBSTRING(OBJECT_NAME(d.depid), 1, 20), col_name = SUBSTRING(name, 1, 15), IsSchemaBound = CASE deptype WHEN 1 THEN 'Schema Bound' ELSE 'Free' END FROM sysdepends d JOIN syscolumns c ON d.depid = c.id AND d.depnumber = c.colid WHERE object_name(d.id) LIKE 'SalesByStore%' RESULTS: obj_name dep_obj col_name IsSchemaBound -------------------- -------------------- --------------- ------------- SalesByStore sales stor_id Schema Bound SalesByStore sales qty Schema Bound SalesByStore sales title_id Schema Bound SalesByStore titles title_id Schema Bound SalesByStore titles title Schema Bound SalesByStore_MS sales stor_id Free SalesByStore_MS sales qty Free SalesByStore_MS sales title_id Free SalesByStore_MS titles title_id Free SalesByStore_MS titles title Free 

To see the impact of schema binding, I'll try to change one of the referenced columns. The following ALTER will attempt to change the datatype of qty from smallint to int:

 ALTER TABLE sales ALTER COLUMN qty int 

I get these error messages:

 Server: Msg 5074, Level 16, State 3, Line 1 The object 'SalesByStore' is dependent on column 'qty'. Server: Msg 4922, Level 16, State 1, Line 1 ALTER TABLE ALTER COLUMN qty failed because one or more objects access this column. 

On the other hand, changing the datatype of the ord_date column from datetime to smalldatetime will succeed because the ord_date column is not referenced by a schema-bound object.

Getting Information About Your Functions

SQL Server 2000 provides many utilities for getting information about your functions. To see the definition of a function, you can use the system procedure sp_helptext or look in the ANSI SCHEMA VIEW called ROUTINES. Both commands shown below will return the definition of my SalesByStore function:

 EXEC sp_helptext SalesByStore SELECT routine_definition FROM INFORMATION_SCHEMA.routines WHERE routine_name = 'SalesByStore' 

Although you can use either sp_helptext or ROUTINES to get information about your own functions, you can't get the definition of the system table-valued functions using sp_helptext. You must use the master database and look in the ROUTINES view. In fact, for some of the supplied system table-valued functions, you won't get the entire definition. You'll get only the declaration section because the rest of the code is hidden. The query below shows the complete definition of a system function:

 USE master SELECT routine_definition FROM INFORMATION_SCHEMA.ROUTINES WHERE routine_name = 'fn_helpcollations' 

You can also get information about your functions from the ANSI SCHEMA VIEWS called ROUTINE_COLUMNS and PARAMETERS. For details about using these views, see SQL Server Books Online.

The metadata function OBJECTPROPERTY also has quite a few property values that are relevant to user-defined functions. For example, I mentioned earlier that you can tell whether a function is a table-valued function by looking at its definition and checking to see whether the RESULTS clause contains the word TABLE. Alternatively, you can use the OBJECTPROPERTY function with the IsTableFunction or 'IsInlineFunction' argument:

 SELECT OBJECTPROPERTY(object_id('SalesByStore'), 'IsInlineFunction') 

Calling the OBJECTPROPERTY function with the 'IsInlineFunction' property parameter returns one of three values. The value 1 means TRUE, the function is an inline table-valued function and 0 means FALSE, the function isn't an inline table-valued function. A NULL will be returned if you typed something wrong, for example, if you supply an invalid object ID, the ID of an object that is not a function, or if you misspell the property name. If you call the OBJECTPROPERTY function with the 'IsTableFunction' property parameter, it returns a 1 if the function is a table-valued function that is not also an inline function. Here are the other parameters of OBJECTPROPERTY that can give you useful information about your functions:

 IsScalarFunction IsSchemaBound IsDeterministic