Shulou(Shulou.com)06/01 Report--

User-defined functions cannot be used to perform a series of operations that change the state of the database, but they can be used in program segments such as queries or stored procedures like system functions, or executed by the EXECUTE command like stored procedures.

In SQL Server, user-defined functions are divided into three types according to the form of the function return value:

(1)scalar function

(2)inline table-valued function

(3)multistatement table-valued function

Note: Unlike functions in programming languages, SQL Server custom functions must have a return value.

scalar function

Scalar functions return a scalar value of a definite type whose return value type is a data type other than TEXT, NTEXT, IMAGE, CURSOR, TIMESTAMP, and TABLE. The function body statement is defined within the BEGIN-END statement. The data type of the return value is defined in the RETURNS clause, and the last statement of the function must be a Return statement. Format for creating scalar functions:

Create Function Name (Parameter)

Returns Return Value Data Type

[With {Encryption|Schemabinding}]

[AS]

BEGIN

SQL statement (must have a Return clause)

END

Examples:

CREATE FUNCTION dbo.Max

(

@a int,

@b int

)

RETURNS int AS

BEGIN

DECLARE @max int

IF @a>@b SET @max=@a

ELSE SET @max=@b

Return @max

END

Call scalar function

Any function that returns a scalar value of the same data type as a scalar expression can be called anywhere in a T-SQL statement that allows the use of scalar expressions. Scalar-valued functions must be called using functions with at least two-part names, i.e. schema names. Object name, such as dbo.Max(12,34).

inline table-valued function

An inline table-valued function returns a return value in the form of a table, that is, it returns a table. Inline table-valued functions do not have a function body enclosed by a BEGIN-END statement. The tables returned are filtered from the database by a SELECT command in the RETURN clause. Inline tabular functions function like a parameterized view.

Create Function Name (Parameter)

RETURNS table

[with {Encryption|Schemabinding}]

AS

Return(a SQL statement)

Examples:

CREATE FUNCTION func (@id char(8))

RETURNS TABLE

AS

RETURN

(

SELECT * FROM student WHERE SID = @id

)

Inline table-valued functions are called without specifying a schema name, such as select * from func ('51300521 ')

multistatement table-valued function

Multi-statement table-valued functions can be viewed as a combination of scalar functions and inline table-valued functions. Its return value is a table, but like scalar functions, it has a function body enclosed in a BEGIN-END statement, and the data in the return value table is inserted by statements in the function body. It can be seen that it can query many times, filter and merge data many times, and make up for the shortcomings of inline table-valued functions.

Create Function Name (Parameter)

RETURNS table variable name (table variable field definition)

[with {Encryption|Schemabinding}]

AS

BEGIN

SQL statement

Return

END

Examples:

CREATE FUNCTION func(@selection int)

RETURNS @table TABLE

(

SID char(4) primary key not null,

SName nvarchar(4) null

)

AS

BEGIN

IF @selection = 0

INSERT INTO @table (SELECT SID,SName FROM student0)

ELSE

INSERT INTO @table (SELECT SID,SName FROM student1)

Return

END

Calling a multi-statement table-valued function: Just like calling an inline table-valued function, you do not need to specify a schema name when calling it.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.