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
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 stored
procedures in which omitting the parameter also implies the default value.
User-defined functions do not support output parameters.
Scalar functions return a single data value of the type defined
in a RETURNS clause. All scalar data types, including bigint and sql_variant,
can be used. The timestamp data type, user-defined data type, and nonscalar
types, such as table or cursor, are not supported. The body of the function,
defined in a BEGIN...END block, contains the series of Transact-SQL statements
that return the value. The return type can be any data type except text, ntext,
image, cursor, and timestamp.
Table-valued functions return a table. For an inline
table-valued function, there is no function body; the table is the result set
of a single SELECT statement. For a multistatement table-valued function, the
function body, defined in a BEGIN...END block, contains the TRANSACT-SQL
statements that build and insert rows into the table that will be returned. For
more information about inline table-valued functions, see Inline User-Defined
Functions. For more information about table-valued functions, see User-Defined
Functions That Return a table Data Type.
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.
No comments:
Post a Comment