Precautions for modifying table-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 table-valued FUNCTION to a scalar-valued FUNCTION or vice versa.

You cannot ALTER FUNCTION to change an inline FUNCTION to a multi-statement FUNCTION or vice versa.

You cannot use ALTER FUNCTION to change a Transact-SQL FUNCTION to a CLR FUNCTION or 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 syntax for modifying a table-valued function using the SSMS database management tool is the same as that for modifying a table-valued function using the T-SQL script.

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

Modify table value functions using t-SQL scripts

Grammar:

Syntax 1: Modify table-valued functions.

Declare database references

Use database name;

go

— Modify table valued functions

alter function [schema_name.] function_name

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

returns table

[with] [encryption][,][schemabinding]

as

return [ ( ] select_stmt [ ) ]

go

Syntax 2: Modify inline table – valued functions.

Declare database references

Use database name;

go

Create table valued functions

create functino [schema_name.] function_name

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

returns @tablename table

(

@parameter_name [as] [type_schema_name.] parameter_data_type [ = default ] ,

. n

)

begin

Insert into @tablename(1, 2,… n)

Select parameter 1, parameter 2… n from table

[inner | left | right] [join] [table] [on] [condition]

[the where condition]

[Group by condition]

Conditions of [having]

[order by condition]

;

return

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.

–@tablename

— Customize the table name

Call syntax:

Select name,… N from Schema name. Function name (parameter value);

Example:

Declare database references

use testss;

go

— Modify inline table value functions

alter function dbo.innertablefun(@ids int=null)

returns @table1 table

(

id int not null,

name nvarchar(100),

sex nvarchar(100),

test3id int,

test3name nvarchar(100)

)

with encryption,schemabinding

as

begin

with tmptable as (

select a.id,a.name,a.sex,b.id as test3id,b.name as test3name

from dbo.test1 as a

inner join dbo.test3 as b on a.classid=b.id

where a.id=@ids

)

insert into @table1(id,name,sex,test3id,test3name)

select id,name,sex,test3id,test3name from tmptable

return;

end;

go

Example result: Shows the result of the creation and the result of the invocation in sequence