SQL Function user-defined functions and function Functions

SQL Function user-defined functions and function Functions

Directory

Background (why do I need to use a custom function if a stored procedure already exists)
Development History
Composition
Usage
Applicability
Notes
Question
Content

Background (why do I need to use a custom function if a stored procedure already exists)

Differences from stored procedures (meaning ):

1. the user-defined functions can be directly used in select and other SQL statements, but the stored procedure is not good.
2. A user-defined function can call other functions or call itself (recursion)
3. You can use custom functions in the table column and CHECK constraints to implement special columns or constraints.
4. udfs do not have any side effects. Function side effects refer to any permanent changes to the state of resources with a function out-of-function scope (such as changes to database tables. The only change that can be made by a statement in a function is a change to a local object (such as a local cursor or local variable) on the function. Operations that cannot be performed in the function include modifying the database table, performing operations on partial cursors not on the function, sending an email, and attempting to modify the directory, and generate the result set returned to the user. Stored procedures do not have this restriction
5. The function can return only one variable. The stored procedure can return multiple

Development History

User-defined functions are supported after SqlServer 2000

Composition

In SQL Server 2000, user-defined functions are divided into three types based on different Function return values: Scalar Function and Inline Function) multi-Statement Function)
Scalar function: a scalar function operates on a single value and returns a single value. Where expressions can be used, scalar functions can be used. Such as left and getdate, which we often use, all belong to scalar functions. Scalar functions in system functions include mathematical functions, date and time functions, string functions, and data type conversion functions.
Embedded Table value function: the function of embedded Table value function is equivalent to a parameterized view. It returns a table, and the inline table-valued functions are not included in the BEGIN-END statement. The returned table is filtered out from the database by a SELECT command segment in the RETURN clause.

Function

Multiple declared table valued functions: can be seen as a combination of scalar and embedded table valued functions. Its return value is a table, but like a scalar function, it has a function body enclosed by the BEGIN-END statement, the data in the returned table is inserted by the statement in the function body. It can be seen that it can perform multiple queries to filter and merge data multiple times, making up for the deficiency of inline Table value functions.

Usage

SQL Server provides different command creation formats for three types of user-defined functions.

(1) The syntax for creating a Scalar User-Defined Function (Scalar functions) is as follows:

Parameters are described as follows:
Owner_name: Specifies the owner of the User-Defined Function.
Function_name: Specifies the name of the User-Defined Function. Database_name.owner_name.function_name should be unique.
@ Parameter_name: defines the name of one or more parameters. A function can define up to 1024 parameters. Each parameter is marked with the "@" symbol. The parameter applies to the entire function. Parameters can only replace constants. They cannot represent names, column names, or other database objects. Custom functions do not support output parameters.
Scalar_parameter_data_type: Specifies the Data Type of a scalar parameter. It can be other data types except TEXT, NTEXT, IMAGE, CURSOR, TIMESTAMP, and TABLE.
Scalar_return_data_type: Specifies the Data Type of the scalar type returned value. It can be other data types except TEXT, NTEXT, IMAGE, CURSOR, TIMESTAMP, and TABLE.
Scalar_expression: Specify the scalar value expression returned by a scalar user-defined function.
Function_body: Specifies a series of Transact-SQL statements, which determine the return value of the function.
ENCRYPTION: ENCRYPTION option. Enable SQL Server to encrypt the declaration of the create function in the system table to prevent the User-Defined FUNCTION from being published as a part of SQL Server replication (Publish ).
SCHEMABINDING: the scheduled binding option binds a user-defined function to the database object it references. If this option is specified, the database objects involved in the function cannot be deleted or modified, unless the function is deleted or removed. Note that the database object to be bound must be in the same database as the function.

(2) create an Inline Table value-Type User-Defined Function (Inline Table-valued Functions)

The syntax is as follows:

Parameters are described as follows:

TABLE: Specify the return value as a TABLE.

Select-stmt: A single SELECT statement to determine the data of the returned table.

Other parameters are the same as those of the scalar user-defined function.

(3) create a user-defined function with multiple declared table values

The syntax is as follows:

Parameters are described as follows:

@ Return_variable: a table variable used to store and accumulate data rows in the returned TABLE. Other parameters are the same as those of the scalar user-defined function.

The following Transact-SQL statements can be used in the function bodies of user-defined functions with multiple declared table values. Assignment statements; Control-of-Flow statements; DECLARE statements defining variables and cursors in the function; SELECT statements; edit the INSERT, UPDATE, and DELETE statements of the table variables defined in the function. The function allows cursor operations such as declaring the game logo, opening the cursor, closing the cursor, and releasing the cursor, for a read cursor, unless the INTO clause is used in the FETCH statement to assign values to a variable, the FETCH statement cannot be used in the function to return data to the client. Non-terministic functions cannot be used in user-defined functions. An uncertainty function is a function that uses the same call parameters to call different values at different times. These functions are shown in Table 13-3 (global variables can also be considered a function ).

Applicability

1. query only, without modifying the database status (modifying or deleting table Records)

2. functions can be used when the result set needs to be obtained through recursion and other methods. functions are flexible.

3. functions can be used when the result set needs to be directly referenced. The result set needs to be reprocessed (that is, the select statement is medium). functions can be used, and functions can be embedded in SQL statements such as select.

Note:

User-defined functions cannot be used to perform a series of operations to change the database status.

Note the following when writing custom functions:

For scalar functions:

1. @ must be added before all input parameters @

2. return after create. The word is returns, not return.

3. What follows returns is not a variable, but a type of return value, such as int and char.

4. In the begin/end statement block, return is used.

Nested table value functions:

1. Only the table can be returned, so the returns must be followed by the TABLE

2. There is no begin/end after AS, and only one return statement is used to return a specific record.

Multi-statement Table value functions:

1. the type of the returned TABLE is defined directly after returns. The first is to define the TABLE name, indicating that @ is added before, then the keyword TABLE, and finally the TABLE structure.

2. In the begin/end statement block, you can directly insert the results to be returned to the table defined in returns. In the final return, the results will be returned.

3. In the end, only return is required. return is not followed by any variable.

Q: A User-Defined Function cannot modify a database, but it can call a stored procedure. Can a user-defined function be executed when a user-defined function calls a stored procedure with database modification operations?

A: custom functions can only call extended stored procedures. However, this function will be deleted in later versions of SQL Server 2008 and does not support extended stored procedures. Therefore, you should avoid using Extended Stored Procedures during development. Therefore, it can be concluded that, in actual development, the function will not call the stored procedure, and thus the database cannot be modified.

Refer:
Http://technet.microsoft.com/zh-tw/library/ms186755.aspx
Http://msdn.microsoft.com/zh-cn/library/ms175200.aspx
Http://www.cnblogs.com/Athrun/archive/2007/07/27/833416.html