ALTER FUNCTION
Alters an existing user-defined function, previously created by executing the CREATE FUNCTION statement, without changing permissions and without affecting any dependent functions, stored procedures, or triggers.
For more information about the parameters used in the ALTER FUNCTION statement, see CREATE FUNCTION.
Syntax
Scalar Functions
ALTER FUNCTION [ owner_name. ] function_name
( [ { @parameter_name scalar_parameter_data_type [ = default ] } [ ,...n ] ] )
RETURNS scalar_return_data_type
[ WITH < function_option> [,...n] ]
[ AS ]
BEGIN
function_body
RETURN scalar_expression
END
Inline Table-valued Functions
ALTER FUNCTION [ owner_name. ] function_name
( [ { @parameter_name scalar_parameter_data_type [ = default ] } [ ,...n ] ] )
RETURNS TABLE
[ WITH < function_option > [ ,...n ] ]
[ AS ]
RETURN [ ( ] select-stmt [ ) ]
Multi-statement Table-valued Functions
ALTER FUNCTION [ owner_name. ] function_name
( [ { @parameter_name scalar_parameter_data_type [ = default ] } [ ,...n ] ] )
RETURNS @return_variable TABLE < table_type_definition >
[ WITH < function_option > [ ,...n ] ]
[ AS ]
BEGIN
function_body
RETURN
END
< function_option > ::=
{ ENCRYPTION | SCHEMABINDING }
< table_type_definition > :: =
( { column_definition | table_constraint } [ ,...n ] )
Arguments
owner_name
Is the name of the user ID that owns the user-defined function to be changed. owner_name must be an existing user ID.
function_name
Is the user-defined function to be changed. Function names must conform to the rules for identifiers and must be unique within the database and to its owner.
@parameter_name
Is a parameter in the user-defined function. One or more parameters can be declared. A function can have a maximum of 1,024 parameters. The value of each declared parameter must be supplied by the user when the function is executed (unless a default for the parameter is defined). When a parameter of the function has a default value, the keyword "default" must be specified when calling the function in order 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.
Specify a parameter name using an at sign (@) as the first character. The parameter name must conform to the rules for identifiers. Parameters are local to the function; the same parameter names can be used in other functions. Parameters can take the place only of constants; they cannot be used in place of table names, column names, or the names of other database objects.
scalar_parameter_data_type
Is the parameter data type. All scalar data types, including bigint and sql_variant, can be used as a parameter for user-defined functions. The timestamp data type is not supported. Nonscalar types such as cursor and table cannot be specified.
scalar_return_data_type
Is the return value of a scalar user-defined function. scalar_return_data_type can be any of the scalar data types supported by SQL Server, except text, ntext, image, and timestamp.
scalar_expression
Specifies that the scalar function returns a scalar value.
TABLE
Specifies that the return value of the table-valued function is a table.
In inline table-valued functions, the TABLE return value is defined through a single SELECT statement. Inline functions do not have associated return variables.
In multi-statement table-valued functions, @return_variable is a TABLE variable, used to store and accumulate the rows that should be returned as the value of the function.
function_body
Specifies that a series of Transact-SQL statements, which together do not produce a side effect, define the value of the function. function_body is used only in scalar functions and multi-statement table-valued functions.
In scalar functions, function_body is a series of Transact-SQL statements that together evaluate to a scalar value.
In multi-statement table-valued functions, function_body is a series of Transact-SQL statements that populate a table return variable.
select-stmt
Is the single SELECT statement that defines the return value of an inline table-valued function.
ENCRYPTION
Indicates that SQL Server encrypts the system table columns containing the text of the CREATE FUNCTION statement. Using ENCRYPTION prevents the function from being published as part of SQL Server replication.
SCHEMABINDING
Specifies that the function is bound to the database objects that it references. This condition will prevent changes to the function if other schema bound objects are referencing it.
The binding of the function to the objects it references is removed only when one of two actions take place:
- The function is dropped.
- The function is altered (using the ALTER statement) with the SCHEMABINDING option not specified.
For a list of conditions that must be met before a function can be schema bound, see CREATE FUNCTION.
Remarks
ALTER FUNCTION cannot be used to change a scalar-valued function to a table-valued function, or vice versa. Also, ALTER FUNCTION cannot be used to change an inline function to a multistatement function, or vice versa.
Permissions
ALTER FUNCTION permissions default to members of the sysadmin fixed server role, and the db_owner and db_ddladmin fixed database roles, and the owner of the function, and are not transferable.
Owners of functions have EXECUTE permission on their functions. However, other users may be granted such permissions as well.