Creating and Managing Functions

The preceding sections of this chapter have already shown examples of creating functions. In this section, we'll discuss in more detail the CREATE FUNCTION syntax and what types of operations are allowed in functions. This section will also show you how to create and manage functions using SQL Enterprise Manager and SQL Query Analyzer.

Creating Functions

Functions are created using Transact-SQL. The Transact -SQL code can be entered in isql, osql, Query Analyzer, or any other third-party query tool that allows you to enter ad hoc T-SQL code. This section first looks at the basic syntax for creating functions, and then looks at how you can create functions using the features of Enterprise Manager and Query Analyzer.

T-SQL

The syntax for the create function command for scalar functions is as follows :

 CREATE FUNCTION [  owner_name  . ]  function_name   (  [ { @  parameter_name scalar_datatype  [ =  default  ] } [  ,...   n  ] ]  )  RETURNS  scalar_datatype  [ WITH {ENCRYPTION , SCHEMABINDING} [ AS ] BEGIN  SQL_Statements  RETURN  scalar_expression  END 

The syntax for the create function command for inline table-valued functions is as follows:

 CREATE FUNCTION [  owner_name  . ]  function_name   (  [ { @  parameter_name scalar_datatype  [ =  default  ] } [  ,...   n  ] ]  )  RETURNS TABLE [ WITH {ENCRYPTION , SCHEMABINDING} [ AS ] RETURN [  (  ]  select-stmt  [  )  ] 

The syntax for the create function command for multistatement table-valued functions is as follows:

 CREATE FUNCTION [  owner_name  . ]  function_name   (  [ { @  parameter_name scalar_datatype  [ =  default  ] } [  ,...   n  ] ]  )  RETURNS @  table_variable  TABLE  (  {  column_definition   table_constraint  } [  ,...   n  ]  )  [ WITH {ENCRYPTION , SCHEMABINDING} [ AS ] BEGIN  SQL_Statments  RETURN END 

The types of SQL statements that are allowed in a function include:

• DECLARE statements to define variables and cursors that are local to the function.

• Assignments of values to variables local to the function using the SET command or an assignment select.

• Cursor operations on local cursors that are declared, opened, closed, and deallocated within the function. FETCH statements must assign values to local variables using the INTO clause.

• Control-of-flow statements such as IF , ELSE , WHILE , GOTO , and so on.

• UPDATE , INSERT , and DELETE statements modifying table variables that are defined within the function.

• EXECUTE statements that call an extended stored procedure. (Any results returned by the extended stored procedure are discarded.)

If you specify the ENCRYPTION option, the SQL statements used to define the function will be stored encrypted in the syscomments table. This prevents anyone from viewing the function source code in the database.

NOTE

If you choose to encrypt the function code, be sure to save a copy of the script used to create the function to a file outside the database in case you ever need to modify the function or re-create it. After the source code for the function is encrypted, you cannot extract the original unencrypted source code from the database.

If a function is created with the SCHEMABINDING option, then the database objects that the function references cannot be altered or dropped unless the function is dropped first, or the function is altered and the SCHEMABINDING option is not specified. A CREATE FUNCTION statement with the SCHEMABINDING option specified will fail unless all of the following conditions are met:

• Any user -defined functions and views referenced within the function are also schema-bound.

• Any objects referenced by the function must be referenced using a two-part name ( owner.object_name ).

• The function and the objects it references must belong to the same database.

• The user executing the CREATE FUNCTION statement has REFERENCES permission on all database objects that the function references.

The following example creates a function with the SCHEMABINDING option specified:

 CREATE FUNCTION AveragePricebyType2 (@price money = 0.0)  RETURNS @table table (type varchar(12) null, avg_price money null) with schemabinding AS begin     insert @table        SELECT type, avg(price) as avg_price              FROM dbo.titles              group by type              having avg(price) > @price     return end 

The following example shows what happens if you try to modify a column in the titles table that is referenced by the function:

 alter table titles alter column price smallmoney null  go Server: Msg 5074, Level 16, State 3, Line 1 The object 'AveragePricebyType2' is dependent on column 'price'. Server: Msg 4922, Level 16, State 1, Line 1 ALTER TABLE ALTER COLUMN price failed because one or more objects access this column. 

The SQL statements within a function cannot generate side effects; that is, the function cannot generate permanent changes to any resource whose scope extends beyond the function. For example, a function cannot modify data in a table, operate on cursors that are not local to the function, create or drop database objects, issue transaction control statements, or generate a result set that is returned to the user. The only changes that can be made by the SQL statements in a function are to the objects that are local to the function, such as local cursors or variables.

In addition, user-defined functions cannot invoke built-in functions that can return different data values on each call. These are referred to as nondeterministic functions. For example, the GETDATE() function is considered nondeterministic because even though it is always invoked with the same argument, it returns a different value each time it is executed. Nondeterministic built-in functions that are not allowed in user-defined functions include the following:

• @@CONNECTIONS

• @@CPU_BUSY

• @@ERROR

• @@IDLE

• @@IO_BUSY

• @@MAX_CONNECTIONS

• @@PACK_RECEIVED

• @@PACK_SENT

• @@PACKET_ERRORS

• @@TIMETICKS

• @@TOTAL_ERRORS

• @@TOTAL_READ

• @@TOTAL_WRITE

• GETDATE()

• GetUTCDate()

• NEWID()

• RAND()

• TEXTPTR()

This is why the getdateonly() function created earlier in this chapter must be passed the date value and cannot invoke the getdate() function directly. This limitation actually helps to extend the functionality of the getdateonly() function because it now can be used to strip the time component off of any date expression instead of just returning the current date.

Creating Functions in Enterprise Manager

To create a function in Enterprise Manager, right-click the database in which you want to create the function, move the cursor to the New menu option, and then choose the New User-Defined Function option from the menu list. Alternatively, you can open the database folder, right-click the User-Defined Functions folder, and choose the New User-Defined Function option. Either approach will bring up the User-Defined Function Properties window shown in Figure 30.1.

Figure 30.1. The New User-Defined Function Properties window.

The Properties window presents you with a basic template for creating a function. You simply fill in the function name and return type spec and function body, coding it just as you would in Transact-SQL. When the information is complete, you can check the syntax before creating the function by clicking the Check Syntax button. Click OK when you are ready to create the function.

Creating Functions in Query Analyzer

You can also create user-defined functions from predefined function templates using Query Analyzer. To open a function template, select File, New from the menu. This will bring up a dialog box with a list of folders of templates. Double-click the Create Function Folder to bring up a list of function templates from which to choose. Currently, SQL Server has three templates:

• Create Inline Function

• Create Scalar Function

• Create Table Function

Double-click the template for the type of function you want to create, or select the template and click the OK button and you will be presented with a Query Analyzer window with the appropriate template code, as shown in Figure 30.2.

Figure 30.2. A Query Analyzer window populated with template code for a function.

Modify the template code as necessary to name the function and to specify the parameters, return value, and function body. When you are finished, execute the contents of the window to create the function. Note that the template also contains template code for executing or testing the created function. You will need to edit, delete, or comment this code out before creating the function to avoid having it generate a syntax error when SQL Server attempts to execute it after creating the function. When you have created the function successfully, it is recommended that you save the source code to a file by choosing the Save or Save As option from the File menu. This way, you can re-create the function from the file if it is accidently dropped from the database.

A quicker way to bring up a function template is to click the drop-down arrow on the New Document icon in the Query Analyzer toolbar and scroll down to the Create Function option, which brings up a menu of function templates from which to choose (see Figure 30.3).

Figure 30.3. Creating a new function template window from the Query Analyzer toolbar.

Viewing and Modifying Functions

Besides creating functions, Transact-SQL commands are also used to view and modify functions. You can get information using the provided system procedures and queries against the system catalog. This section describes these methods .

Viewing Functions with Transact-SQL

To view the source code for a user-defined function, you can use the sp_helptext procedure:

 exec sp_helptext getdateonly  go Text ------------------------------------------------------------------------- create function dbo.getdateonly(@datetime datetime) returns datetime as begin declare @date datetime set @date = convert(datetime, convert(char(10), @datetime, 110)) return @date end 

In addition to sp_helptext , you can write queries against the INFORMATION_SCHEMA view routines to display the source code for a function:

 select routine_definition  from INFORMATION_SCHEMA.routines where routine_name = 'getdateonly' go routine_definitionz ------------------------------------------------------------------------------- create function dbo.getdateonly(@datetime datetime) returns datetime as begin declare @date datetime set @date = convert(datetime, convert(char(10), @datetime, 110)) return @date end 

If you want to display information about the input parameters for a function, use the INFORMATION_SCHEMA view PARAMETERS . For scalar functions, the view will also display information for the return parameter, which will have an ordinal position of 0 and no parameter name:

 select substring(parameter_name,1,30) as parameter_name,         substring(datatype, 1, 30) as datatype,        Parameter_mode,        ordinal_position from INFORMATION_SCHEMA.parameters where specific_name = 'getdateonly' order by ordinal_position go parameter_name        datatype       Parameter_mode ordinal_position --------------------- -------------- -------------- ----------------                       datetime       OUT                           0 @datetime             datetime       IN                            1 

If you want to display information about the result columns returned by a table-valued function, use the INFORMATION_SCHEMA view ROUTINE_COLUMNS :

 select substring(column_name, 1, 30) as column_name,         substring (datatype, 1, 20)              + case when character_maximum_length is not null                  then '(' + cast(character_maximum_length as varchar(4)) + ')'                       else ''                       end               as datatype,        numeric_precision,        numeric_scale,        ordinal_position from INFORMATION_SCHEMA.routine_columns where table_name = 'AveragePricebyType' order by ordinal_position go column_name      datatype    numeric_precision numeric_scale ordinal_position ---------------- ------------ ----------------- ------------- ---------------- type             char(12)                     0 NULL                         1 avg_price        money                       19             4                2 

Additionally, SQL Server provides the OBJECTPROPERTY function, which you can use to get information about your functions. One of the things you can find out is whether the function is a multistatement table function, an inline function, or a scalar function. The OBJECTPROPERTY function accepts an object ID and an object property parameter and returns the value of 1 if the property is TRUE, 0 if it is FALSE, or NULL if an invalid function ID or property parameter is specified. The following is a list of the property parameters appropriate for functions:

• IsTableFunction ” Returns 1 if function is a table-valued function but not an inline function

• IsInlineFunction ” Returns 1 if function is an inline table-valued function

• IsScalarFunction ” Returns 1 if function is a scalar function

• IsSchemaBound ” Returns 1 if function was created with SCHEMABINDING option

• IsDeterministic ” Returns 1 if function is deterministic; that is, it always returns the same result each time it is called with a specific set of input values

The following example demonstrates a possible use of the OBJECTPROPERTY function with the INFORMATION_SCHEMA.routines view:

 select convert(varchar(30), specific_name) as 'function',    case objectproperty(object_id(specific_name), 'IsScalarFunction')       when 1 then 'Yes' else 'No' end as IsScalar,   case objectproperty(object_id(specific_name), 'IsTableFunction')       when 1 then 'Yes' else 'No' end as IsTable,   case objectproperty(object_id(specific_name), 'IsInlineFunction')       when 1 then 'Yes' else 'No' end as IsInline,   case objectproperty(object_id(specific_name), 'IsSchemaBound')       when 1 then 'Yes' else 'No' end as IsSchemaBound,   case objectproperty(object_id(specific_name), 'IsDeterministic')       when 1 then 'Yes' else 'No' end as IsDeterministic from information_Schema.routines where routine_type = 'FUNCTION' order by specific_name go function              IsScalar IsTable IsInline IsSchemaBound IsDeterministic --------------------- -------- ------- -------- ------------- --------------- AverageBookPrice      Yes      No      No       No            No AveragePricebyType    No       No      Yes      No            No AveragePricebyType2   No       Yes     No       Yes           No valid_book_types      No       No      Yes      No            No 

Modifying Functions with Transact-SQL

You can use the ALTER FUNCTION command to change the function's definition without having to drop and re-create it. The syntax for the ALTER FUNCTION command is identical to the CREATE FUNCTION syntax except for replacing the CREATE keyword with the ALTER keyword. The following example modifies the AveragePricebyType2 function:

 ALTER FUNCTION AveragePricebyType2 (@price money = 0.0)  RETURNS @table table (type varchar(12) null, avg_price money null) with schemabinding AS begin     insert @table        SELECT type, avg(price) as avg_price              FROM dbo.titles              group by type              having avg(price) > @price         order by avg(price) desc     return end 

The ALTER FUNCTION command has a couple of advantages over dropping and re-creating the function to modify it. The main advantage is that you don't have to drop the function first to make the change. The second advantage is that because you don't have to drop the function, you don't have to worry about reassigning permissions to the function.

The main disadvantage of using ALTER FUNCTION instead of dropping and re-creating the function is that the date of the function modification is not recorded in the database catalogs. As a DBA, it is not possible to tell which functions have been modified since they were created, making it difficult to selectively extract the code for only the functions that have been modified.

Also, you cannot use ALTER FUNCTION to change a table-valued function to a scalar function or to change an inline function to a multistatement function. You'll have to drop and re-create it.

You can also view and modify functions in Enterprise Manager and Query Analyzer.

Viewing and Modifying Functions in Enterprise Manager

To view and edit a function in Enterprise Manager, browse to the User-Defined Functions folder within the appropriate database folder, and then either double-click the function name or right-click the function name and select the Properties option (see Figure 30.4) to bring up the function editor.

Figure 30.4. Invoking the function editor in Enterprise Manager.

This is the same editor you used to create a function in Enterprise Manager (refer to Figure 30.2), except that now it contains the actual function source code instead of template code.

The function editor in Enterprise Manager is not elegant. It lacks any sort of search and replace capability as well as the ability to save the function code to a file. One nice feature, however, is the ability to check the function syntax before applying changes by clicking the Check Syntax button. The actual changes to the function will be applied when clicking the Apply or OK button.

Enterprise Manager applies changes to the stored procedure by using the ALTER FUNCTION . The creation date of the function will not be updated.

Viewing and Modifying Functions in Query Analyzer

If you want to modify a function by dropping and re-creating it, Query Analyzer is the better tool to use. Besides having a full-featured editor, Query Analyzer provides options for how to extract the function source code to modify and apply changes. It will generate code to create, alter, or drop the selected function. You can choose to script the function source code to a new window, to a file, or to the Windows Clipboard by right-clicking the function name in the Object Browser and choosing the appropriate option (see Figure 30.5).

Figure 30.5. Extracting function source code to an editor window in Query Analyzer.

Query Analyzer provides a number of options for how to extract the stored procedure source code. The script generated can automatically include the command to check for the existence of the object and automatically drop it before executing the CREATE FUNCTION command. You can also choose to include the commands to reset permissions when the function is re-created. To ensure these features are included, make sure the following options are set within Query Analyzer:

• Generate Transact-SQL to remove referenced component. Script tests for existence before attempting to remove component.

• Script object-level permissions.

You can set the scripting options by selecting the Script tab in the Options dialog box. The Options dialog box can be invoked by selecting it from the Tools menu, by pressing Ctrl+Shift+O, or by right-clicking the function name in the Object Browser and choosing the Scripting Options menu option. This will bring up the Scripting Options dialog box, as shown in Figure 30.6.

Figure 30.6. Setting scripting options in Query Analyzer.

Listing 30.1 shows an example of the script generated by Query Analyzer when the options selected are in effect. The changes can be implemented by simply executing the SQL script in Query Analyzer.

Listing 30.1 Example of Function Creation Script Generated by Query Analyzer

 SET QUOTED_IDENTIFIER OFF GO SET ANSI_NULLS OFF GO /****** Object:  User Defined Function dbo.AveragePricebyType2         Script Date: 5/25/2001 3:08:53 AM ******/ if exists (select * from dbo.sysobjects    where id = object_id('dbo.AveragePricebyType2')      and xtype in ('FN', 'IF', 'TF')) drop function dbo.AveragePricebyType2 GO CREATE FUNCTION AveragePricebyType2 (@price money = 0.0) RETURNS @table table (type varchar(12) null, avg_price money null) --with schemabinding AS begin     insert @table        SELECT type, avg(price) as avg_price              FROM dbo.titles              group by type              having avg(price) > @price     return end GO SET QUOTED_IDENTIFIER OFF GO SET ANSI_NULLS ON GO 

Managing User-Defined Function Permissions

When a function is initially created, the only user who has permission to execute the function is the user who created it. To allow other users to execute a scalar function, you need to grant EXECUTE permission on the function to the appropriate user(s), group(s), or role(s). For a table-valued function, you need to grant SELECT permission to the user(s), group(s), or role(s) that will need to reference it. In the following example, you are granting EXECUTE permission on the getdateonly() function to everyone and SELECT permission on the AveragePriceByType function to the database user fred :

 grant execute on dbo.getdateonly to public  grant select on AveragePricebyType to fred 

For more detailed information on granting and revoking permissions, see Chapter 15, "Security and User Administration."