SQL function Custom Functions detailed _mssql

Directory

Generate background (stored procedures already available, why use custom functions)
Development history
Constitute
How to use
Applicable scope
Attention matters
Questions
content

Generate background (stored procedures already available, why use custom functions)

Differences from stored procedures (meaning of existence):

1. The ability to use custom functions directly in SQL statements such as select can not be stored procedures.
2. Custom functions can call other functions, or they can call themselves (recursive)
3. You can use custom functions in table columns and CHECK constraints to implement special columns or constraints
4. Custom functions cannot have any side effects. function side effects are any permanent changes to the state of a resource that has a scope outside the function, such as a modification of a database table. The only change that a statement in a function can make is a change to a local object (such as a local cursor or a local variable) on a function. Actions that cannot be performed in a function include modifying a database table, manipulating a local cursor that is not on a function, sending an e-mail message, attempting to modify a directory, and generating a result set that is returned to a user. Stored procedures do not have this restriction
5. A function can return only one variable. And a stored procedure can return multiple

Development history

User-defined functions are supported after SQL Server 2000

Constitute

In SQL Server 2000, user-defined functions are divided into three types based on the form of function return values: Scalar function (Scalar function), inline table-valued function (Inline function), multiple-declaration table-valued function (multi-statement Function)
Scalar functions: Scalar functions are for a single value operation, return a single value. Where you can use an expression, you can use a scalar function. As we often use left, GETDATE, etc., are scalar functions. scalar functions in system functions include: Mathematical functions, date and time functions, string functions, data type conversion functions, etc.
Inline table-valued Function: The function of inline table-valued functions is equivalent to a parameterized view. It returns a table in which the inline table-valued function does not have a function body enclosed by the Begin-end statement. The table it returns is filtered from the database by a SELECT command segment that is located in the return clause.

Role

Multiple Declaration table-valued function: It can be considered as a combination of scalar and inline table-valued functions. Its return value is a table, but like a scalar function, it has a function body enclosed in the Begin-end statement, and the data in the table that returns the value is inserted by the statement in the function body. This shows that it can be repeatedly queried, the data for multiple screening and merging, to make up for the insufficiency of the inline table-valued function.

How to use

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

(1) Create a scalar user-defined function (Scalar functions) with the following syntax:

The parameters are described as follows:
Owner_name: Specifies the owner of the user-defined function.
Function_name: Specifies the name of the user's custom function. Database_name.owner_name.function_name should be the only one.
@parameter_name: Defines the name of one or more parameters. A function can define up to 1024 parameters per parameter before each argument with the "@" symbol. The scope of the parameter is the entire function. Parameters can only be substituted for constants, not for names, column names, or other database objects. Output parameters are not supported by user-defined functions.
Scalar_parameter_data_type: Specifies the data type of a scalar parameter, which can be a data type other than text, NTEXT, IMAGE, CURSOR, TIMESTAMP, and table types.
Scalar_return_data_type: Specifies the data type of a scalar return value, which can be a data type other than text, NTEXT, IMAGE, CURSOR, TIMESTAMP, and table types.
Scalar_expression: Specifies the scalar-valued expression returned by a scalar user-defined function.
Function_body: Specifies a series of Transact-SQL statements that determine the return value of the function.
Encryption: Encryption option. Let SQL Server encrypt the declaration for the CREATE function in the system table to prevent user-defined functions from being published as part of SQL Server replication (Publish).
Schemabinding: The schedule 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 will not be deleted or modified, unless the function is deleted or removed from this option. It should be noted that the database object to bind must be in the same database as the function.

(2) Create inline table-valued user-defined functions (Inline table-valued functions)

The syntax is as follows:

The parameters are described as follows:

Table: Specifies that the return value is a table.

SELECT-STMT: A single SELECT statement that determines the data for the returned table.

The remaining parameters are the same as scalar user-defined functions.

(3) Create multiple Declaration table-valued user-defined functions

The syntax is as follows:

The parameters are described as follows:

@return_variable: A variable of type table that is used to store and accumulate rows of data in the returned table. The remaining parameters are the same as scalar user-defined functions.

The following Transact-SQL statements are allowed in the function body of a multiple declaration table-valued user custom function. Assignment statements (Assignment statements); Process Control statements (Control-of-flow statements); Declare statements that define the variables and cursors within the function range, and the SELECT statement; An insert, UPDATE, and delete statement that edits the table variables defined in the function, allowing cursor operations such as declaring a cursor, opening a cursor, closing a cursor, releasing a cursor in a function, and, for reading a cursor, assigning a variable unless an into clause is used in a fetch statement. Otherwise, you are not allowed to use the FETCH statement in a function to return data to the client. In addition, nondeterministic functions (non-deterministic functions) cannot be used in user-defined functions. The so-called nondeterministic functions are those that use the same invocation parameters at different times to get the return value of different functions. These functions are shown in table 13-3 (global variables can also be considered as a function).

Applicable scope

1. Query only, do not modify the status of the database (modify, delete the records in the table, etc.)

2. When the result set needs to be obtained by recursion, the function can be used and the function is more flexible.

3. A function can be used when a result set needs to be referenced directly. You need to process the result set (which is in the middle of the SELECT statement), you can use functions, and functions can be embedded in SQL statements such as SELECT.

Precautions:

User-defined functions cannot be used to perform a series of operations that alter the state of the database

What you need to be aware of when writing a custom function:

For scalar functions:

1. All incoming parameters must be added @

2. After the return of the Create, the word is returns, instead of returning

3. After the returns is not a variable, but the return value of the type, such as: Int,char.

4. In the Begin/end statement block, is return.

Inline table-valued functions:

1. Only return table, so returns must be a table behind

2. As after no begin/end, only one return statement returns a specific record.

Multi-statement table-valued Function:

1. After returns directly define the returned table type, the first is to define the table name, indicating that you want to precede the @, then the keyword table, and finally the structure of the table.

2. In the Begin/end statement block, it is OK to insert the result that needs to be returned directly into the table defined by the returns, and return the result when the return is final.

3. Finally only need to return,return after not with any variables.

Question: A custom function cannot modify a database, but it can invoke a stored procedure, and then call a stored procedure in a custom function that has an operation to modify the database, can this custom function execute?

A: The custom function can only invoke extended stored procedures, but subsequent versions of SQL Server 2008 will remove the feature and no longer support extended stored procedures, so you should avoid using extended stored procedures in your development. Therefore, it can be concluded that in actual development, the function does not call the stored procedure, and cannot modify the database operation.

Reference:
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