User-defined Functions

A user-defined function cannot be used to perform a series of operations that alter the state of a database, but it can be used like a system function in a program segment such as a query or stored procedure, or it can be executed as a stored procedure through an execute command.

The user-defined function is divided into three types in SQL Server, depending on the form of the function return value:

(1) Scalar function

(2) Inline table-valued functions

(3) Multi-statement table-valued function

Note: Unlike a function in a programming language, a SQL Server custom function must have a return value.

Scalar functions

Scalar functions return a scalar value that determines the type of the return value type other than text, NTEXT, IMAGE, CURSOR, timestamp, and table type. 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. To create a scalar function format:

Create function name (parameter)

Returns return Value data type

[With {encryption| Schemabinding}]

[As]

BEGIN

SQL statement (must have a return clause)

END

Example:

CREATE FUNCTION dbo. Max

(

@a int,

@b int

)

RETURNS int AS

BEGIN

DECLARE @max int

IF @a>@b SET @[email protected]

ELSE SET @[email protected]

Return @max

END

Calling scalar functions

Any function that returns a scalar value (the same as the data type of a scalar expression) can be called anywhere in a T-SQL statement that uses a scalar expression. You must use a function that has at least two parts of the name to call a scalar-valued function, the schema name. The name of the object, such as Dbo.max (12,34).

Inline table-valued functions

The 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 begin-end statements. The table it returns is filtered from the database by a SELECT command located in the return clause. Inline table-valued functions are equivalent to a parameterized view.

Create function name (parameter)

RETURNS table

[With {encryption| Schemabinding}]

As

Return (an SQL statement)

Example:

CREATE FUNCTION func (@id char (8))

RETURNS TABLE

As

RETURN

(

SELECT * FROM student WHERE SID = @id

)

Call inline table-valued Function: Call without specifying the schema name, such as SELECT * from func (' 51300521 ')

Multi-statement table-valued functions

Multi-statement table-valued functions can be considered as a combination of scalar functions and inline table-valued functions. Its return value is a table, but it has the same function as a scalar type function that is enclosed in a begin-end statement, and the data in the table that returns the value is inserted by the statement in the body of the function. Thus, it can make multiple queries, filter and merge the data several times, make up the insufficiency of the inline table-valued function.

Create function name (parameter)

RETURNS table variable name (table variable field definition)

[With {encryption| Schemabinding}]

As

BEGIN

SQL statements

Return

END

Example:

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

Call a multi-statement table-valued function: like calling inline table-valued functions, you do not need to make schema names when calling.

User-defined Functions