ALTER FUNCTION

Transact-SQL Reference

Transact-SQL Reference

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.

See Also

CREATE FUNCTION

DROP FUNCTION