User-defined functions in SQL server (I) Scalar Value Functions

------------------ UDF --------------
-- Create a user-defined function. This is a stored Transact-SQL or Common Language Runtime (CLR) routine,
-- This routine returns a value. User-defined functions cannot be used to modify the database status.
-- Like system functions, user-defined functions can be called from queries. Like a stored procedure, scalar functions can be executed using EXECUTE statements.

Syntax format of scalar functions

Create function [schema_name.] function_name
([{@ Parameter_name [AS] [type_schema_name.] parameter_data_type
[= Default]}
[,... N]
]
)
RETURNS return_data_type
[WITH <function_option> [,... n]
[AS]
BEGIN
Function_body
RETURN scalar_expression
END
[;]

Create a simple scalar function

User-defined functions cannot be used to modify the database status. They cannot be used to modify, add, or delete a table.

The following statements are valid in the function:

• Value assignment statement.
• A stream control statement other than a TRY... CATCH statement.
• DECLARE statements that define local data variables and local cursors.
• SELECT statement. The selection list contains the expression for allocating values to local variables.
• A cursor operation that references a local cursor declared, opened, closed, and released in a function. Only FETCH statements that assign values to local variables using the INTO clause are allowed. FETCH statements that return data to the client are not allowed.
• Modify the INSERT, UPDATE, and DELETE statements of local table variables.
• Call the EXECUTE statement of the extended stored procedure.

User-defined functions can be nested

That is to say, user-defined functions can be called to each other. When the called function starts execution, the nested level is increased. After the called function is executed, the nested level is reduced. The nesting level of user-defined functions can be up to 32. If the maximum number of nested levels is exceeded, the entire call function chain fails.

 

Code
Create function HelloWordFunction (@ Text VARCHAR (10 ))

-- The parameter type and length must be specified. If this parameter is not specified, the length of the value is lost.

-- Specify the length of the returned value.
Returns varchar (10)
BEGIN
RETURN @ Text
END

 

Call Method

Method 1: a scalar function is the same as a stored procedure and can be executed using an EXECUTE statement.

Code
DECLARE @ ReturnValue VARCHAR (10)
EXECUTE @ ReturnValue = dbo. HelloWordFunction 'hellowrod'
PRINT @ ReturnValue

Result:

 

Method 2: direct call

 

Code
PRINT dbo. [HelloWordFunction] ('hello ')