Page 80 - DCAP508_DATABASE_ADMINISTRATION

Database Administration

Notes

5.10.2 Functions

Functions are subroutines made up of one or more Transact-SQL statements that can be used to
encapsulate code for reuse. Microsoft® SQL Server™ 2000 does not limit users to the built-in
functions defined as part of the Transact-SQL language, but allows users to create their own user-
defined functions.
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 (database_name.owner_name.function_name) must be
unique.

You must have been granted CREATE FUNCTION permissions to create, alter, or drop user-
defined functions. Users other than the owner must be granted appropriate permissions on a
function before they can use it in a Transact-SQL statement. To create or alter tables with references
to user-defined functions in the CHECK constraint, DEFAULT clause, or computed column
definition, you must also have REFERENCES permission on the functions.
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.
Types of User – Defined Functions

SQL Server 2000 supports three types of user-defined functions:
Scalar functions
Inline table-valued functions
Multistatement table-valued functions

74 LOVELY PROFESSIONAL UNIVERSITY