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.
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.
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.
NOTEIf 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.
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.
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.
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.
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).
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.
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
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.
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.
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.
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).
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.
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.
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
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."