MS SQL Basics Tutorial: User-defined functions

In addition to using system-supplied functions, users can also customize functions as needed. User Defined functions is a new database object for SQL Server 2000 and is a great improvement for SQL Server.

User-defined functions cannot be used to perform a series of operations that alter the state of a database, but it can be used as a system function in a program segment such as a query or stored procedure, or it can be executed as a stored procedure through the Execute command. A Transact-SQL routine is stored in a user-defined function and can return a certain value.

In SQL Server 2000, user-defined functions are divided into three types based on the form of function return values: Scalar functions (Scalar functions)

Scalar functions return a scalar value of a certain type whose return value type is a data type other than text, NTEXT, IMAGE, CURSOR, TIMESTAMP, and table types. The function body statement is defined within the Begin-end statement, which contains Transact-SQL commands that can return a value. Inline table-valued function (Inline table-valued functions)

An inline table-valued function returns a return value in the form of a table that returns a function body that is not surrounded by begin-end statements by a table-valued function. The table it returns is filtered from the database by a SELECT command segment that is located in the return clause. Inline table-valued function functions are equivalent to a parameterized view. Multiple Declaration table-valued function (multi-statement table-valued functions)

Multi-declaration table-valued functions 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 a number of queries, the data for multiple screening

And merging, make up for the insufficiency of the inline table-valued function.

13.13.1 create user-defined functions

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