Creating User-defined Functions (Database Engine)

  • Article

User-defined functions are created using the CREATE FUNCTION statement, modified using the ALTER FUNCTION statement, and removed using the DROP FUNCTION statement. Each fully qualified user-defined function name (schema_name.function_name) must be unique.

Guidelines

Transact-SQL errors that cause a statement to be canceled and continue with the next statement in the module (such as triggers or stored procedures) are treated differently inside a function. In functions, such errors cause the execution of the function to stop. This in turn causes the statement that invoked the function to be canceled.

The statements in a BEGIN...END block cannot have any side effects. Function side effects are any permanent changes to the state of a resource that has a scope outside the function such as a modification to a database table. The only changes that can be made by the statements in the function are changes to objects local to the function, such as local cursors or variables. Modifications to database tables, operations on cursors that are not local to the function, sending e-mail, attempting a catalog modification, and generating a result set that is returned to the user are examples of actions that cannot be performed in a function.

The number of times that a function specified in a query is actually executed can vary between execution plans built by the optimizer. An example is a function invoked by a subquery in a WHERE clause. The number of times the subquery and its function is executed can vary with different access paths chosen by the optimizer.

Valid Statements in a Function

The types of statements that are valid in a function include:

  • DECLARE statements can be used to define data variables and cursors that are local to the function.
  • Assignments of values to objects local to the function, such as using SET to assign values to scalar and table local variables.
  • Cursor operations that reference local cursors that are declared, opened, closed, and deallocated in the function. FETCH statements that return data to the client are not allowed. Only FETCH statements that assign values to local variables using the INTO clause are allowed.
  • Control-of-flow statements except TRY...CATCH statements.
  • SELECT statements containing select lists with expressions that assign values to variables that are local to the function.
  • UPDATE, INSERT, and DELETE statements modifying table variables that are local to the function.
  • EXECUTE statements calling an extended stored procedure.

Built-in System Functions

In SQL Server 2005, the following nondeterministic built-in functions can be used in Transact-SQL user-defined functions.

CURRENT_TIMESTAMP

@@MAX_CONNECTIONS

GET_TRANSMISSION_STATUS

@@PACK_RECEIVED

GETDATE

@@PACK_SENT

GETUTCDATE

@@PACKET_ERRORS

@@CONNECTIONS

@@TIMETICKS

@@CPU_BUSY

@@TOTAL_ERRORS

@@DBTS

@@TOTAL_READ

@@IDLE

@@TOTAL_WRITE

@@IO_BUSY

 

The following nondeterministic built-in functions cannot be used in Transact-SQL user-defined functions.

NEWID

RAND

NEWSEQUENTIALID

TEXTPTR

For a list of deterministic and nondeterministic built-in system functions, see Deterministic and Nondeterministic Functions.

Schema-bound Functions

CREATE FUNCTION supports a SCHEMABINDING clause that binds the function to the schema of any objects it references, such as tables, views, and other user-defined functions. An attempt to alter or drop any object referenced by a schema-bound function fails.

These conditions must be met before you can specify SCHEMABINDING in CREATE FUNCTION:

  • All views and user-defined functions referenced by the function must be schema-bound.
  • All objects referenced by the function must be in the same database as the function. The objects must be referenced using either one-part or two-part names.
  • You must have REFERENCES permission on all objects (tables, views, and user-defined functions) referenced in the function.

You can use ALTER FUNCTION to remove the schema binding. The ALTER FUNCTION statement should redefine the function without specifying WITH SCHEMABINDING.

Specifying Parameters

A user-defined function takes zero or more input parameters and returns either a scalar value or a table. A function can have a maximum of 1024 input parameters. When a parameter of the function has a default value, the keyword DEFAULT must be specified when calling the function to get the default value. This behavior is different from parameters with default values in user-defined stored procedures in which omitting the parameter also implies the default value. User-defined functions do not support output parameters.

See Also

Concepts

Creating CLR Functions
Creating User-defined Aggregates
Executing User-defined Functions (Database Engine)
Viewing User-defined Functions

Other Resources

CREATE FUNCTION (Transact-SQL)

Help and Information

Getting SQL Server 2005 Assistance