Precautions for modifying scalar valued functions

Change existing Transact-SQL or CLR functions previously created by executing the CREATE FUNCTION statement without changing permissions or affecting any associated functions, stored procedures, or triggers.

You cannot ALTER FUNCTION to change a scalar-valued FUNCTION to a table-valued FUNCTION. And vice versa.

You cannot change a Transact-SQL FUNCTION to a CLR FUNCTION using ALTER FUNCTION. And vice versa.

You need to have ALTER permission on a function or schema. If a function specifies a user-defined type, you need to have the EXECUTE privilege on that type.

The SSMS database management tool has the same syntax for modifying scalar value functions as t-SQL scripts.

The following Service Broker statements cannot be included in the definition of transact-SQL user-defined functions: BEGIN DIALOG CONVERSATION END CONVERSATION GET CONVERSATION GROUP MOVE CONVERSATION RECEIVE SEND

Use t-SQL scripts to modify scalar value functions

Grammar:

Declare database references

Use database name;

go

— Modifies scalar valued functions

alter function [schema_name.] function_name

(@parameter_name [as] [type_schema_name.] parameter_data_type [ = default ] [ readonly],.. n)

returns return_data_type

[with] [encryption][,][schemabinding][,][returns null on null input | called on null input][,][execute as Clause]

as

begin

function_body;

return scalar_expression;

end

go

Syntax:

–schema_name

— The name of the schema to which the user-defined function belongs.

–function_name

— User-defined function name. Function names must conform to the rules for identifiers and be unique to the database and its schema, even if no parameters are specified, the function name is followed by parentheses.

–@parameter_name

— User-defined function parameters. One or more parameters can be declared.

A function can have a maximum of 2,100 arguments. When executing a function, if default values for parameters are not defined, the user must provide the value for each declared parameter.

— Specify the parameter name by using the at symbol (@) as the first character. Parameter names must comply with the rules for identifiers. Parameters are local parameters corresponding to functions; The same parameter names can be used in other functions.

Parameters can only be used in place of constants and cannot be used in place of table names, column names, or other database object names.

–[ type_schema_name. ] parameter_data_type

— The data type of the parameter and the schema to which it belongs, the latter being optional. For Transact-SQL functions, all data types except timestamp data types (including CLR user-defined types and user-defined table types) are allowed.

For CLR functions, all data types (including CLR user-defined types) are allowed except text, ntext, image, user-defined table types, and TIMESTAMP data types. In transact-SQL functions or CLR functions,

The nonscalar types CURSOR and table cannot be specified as parameter data types.

Scalar_parameter_data_type if type_schema_name is not specified, the database engine looks for scalar_parameter_data_type in the following order:

Schema containing data type names for SQL Server systems

— The default schema for the current user in the current database.

— The DBO schema in the current database.

–[ =default ]

— The default value of the parameter. If the default value is defined, the function can be executed without specifying the value of this parameter.

The keyword DEFAULT must be specified when the function is called to retrieve the DEFAULT value if its arguments have DEFAULT values. This behavior is different from using a parameter with a default value in a stored procedure, in which no parameter is provided means using the default value as well.

The DEFAULT keyword is not required when a scalar function is called by using the EXECUTE statement.

–readonly

— Indicates that parameters cannot be updated or modified in a function definition. If the parameter type is a user-defined table type, you should specify READONLY.

–return_data_type

— The return value of a scalar user-defined function. For transact-SQL functions, all data types except the TIMESTAMP data type (including CLR user-defined types) can be used.

For CLR functions, all data types (including CLR user-defined types) are allowed except text, ntext, image, and TIMESTAMP data types. The non-scalar types CURSOR and table cannot be specified as return data types in transact-SQL or CLR functions.

–encryption

— Applicable to SQL Server 2008 to SQL Server 2017.

— indicates that the database engine will convert the raw text of the CREATE FUNCTION statement to a fuzzy format. The output of fuzzy code cannot be displayed directly in any directory view. Users who do not have access to system tables or database files cannot retrieve fuzzy text.

However, this text can be used by privileged users who have access to system tables through the DAC port or privileged users who have direct access to database files. In addition, users who can attach a debugger to a server process can retrieve the original procedure from memory at run time.

Use this option to prevent functions from being published as part of the SQL Server replication. You cannot specify this option for CLR functions.

–schemabinding

— Specifies that the function is bound to the database object it references. If SCHEMABINDING is specified, the base object cannot be modified in a way that will affect the function definition. You must first modify or delete the function definition itself before you can remove the dependencies of the object to be modified.

–returns null on null input | called on null input

— Specifies the OnNULLCall property of the scalar-valued function. If not specified, the default is CALLED ON NULL INPUT. This means that the function body will be executed even if the argument passed is NULL.

If RETURNS NULL ON NULL INPUT is specified in a CLR function, it indicates that when any of the arguments received by SQL Server are NULL, it can return NULL without actually calling the function body. If the CLR function method specified in <method_specifier> already has a custom property indicating RETURNS NULL ON NULL INPUT,

If the CREATE FUNCTION statement instructs CALLED ON NULL INPUT, the attribute indicated by the CREATE FUNCTION statement takes precedence. The OnNULLCall attribute cannot be specified for CLR table valued functions.

– the execute as clause

EXECUTE AS is required for natively compiled scalar user-defined functions.

–SELF

–EXECUTE AS SELF is equivalent to EXECUTE AS user_name, where the user is specified AS the user who creates or changes the module. The actual user ID of the user who created or changed the module is stored in the execute_AS_PRINCIPal_id column of the sys.SQL_modules or sys.service_queues directory view.

–OWNER

— Specifies that statements within a module are executed in the context of the current owner of the module. If the module does not have a specified owner, the owner of the module schema is used. You cannot specify an OWNER for DDL or login trigger.

–‘ user_name ‘– specifies that statements within the module will be executed in the context of the user specified by user_name. Permissions on any object in the module will be verified against user_name. User_name cannot be specified for DDL triggers or login triggers that have server scope. Please use login_name instead.

User_name must exist in the current database and must be a single instance account. User_name cannot be a group, role, certificate, key, or built-in account, such as NT AUTHORITY\LocalService, NT AUTHORITY\NetworkService, or NT AUTHORITY\LocalSystem.

The user ID of the execution context is stored in the metadata and can be viewed in the execute_AS_principal_id column of the sys.sql_modules or sys.assembly_modules directory view.

–‘ login_name ‘

— Specifies that statements within the module are executed in the context of the SQL Server login specified by login_name. Permissions on any object in the module will be verified based on login_name. Login_name can only be specified for DDL or login triggers with server scope.

Login_name cannot be a group, role, certificate, key, or built-in account, such as NT AUTHORITY\LocalService, NT AUTHORITY\NetworkService, or NT AUTHORITY\LocalSystem.

–function_body

— Specifies a series of Transact-SQL statements that define function values that can be used together without negative effects (such as modifying tables). Function_body is only used for scalar functions and multi-statement table-valued functions.

In scalar functions, function_body is a series of Transact-SQL statements that can be used together to compute scalar values.

In multi-statement table-valued functions, function_body is a series of Transact-SQL statements that populate the TABLE return variables.

–scalar_expression

— Specifies the scalar value returned by a scalar function.

Syntax call:

Select [schema name]. Function name;

Example:

Declare database references

use testss;

go

Alter database;

alter function [dbo].[noreferencepro1](@num1 int,@num2 int)

returns int

with encryption,schemabinding,called on null input,execute as self

as

begin

declare @sum int;

if @num1 is not null and @num2 is not null

set @sum=@num1+@num2;

else if @num1 is not null

set @sum=@num1;

else if @num2 is not null

set @sum=@num2;

else

set @sum=0;

return @sum;

end;

go

Example result: Shows the result of the modification and the result of the invocation in sequence.