SQL Server 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 like a system letter
Used in a program segment, such as a query or stored procedure, or as a stored procedure.
Execute command. User-defined in SQL Server based on function return value as different form
There are three types of semantic functions:

(1) Scalar function

(2) Inline table-valued functions

(3) Multi-statement table-valued function

(1) scalar function
a scalar function returns a scalar value that determines the type of return value, except TEXT, NTEXT,
other data types outside the IMAGE, CURSOR, TIMESTAMP, and TABLE types. function Body Statement definition
within the Begin-end statement. The data type of the return value is defined in the RETURNS clause, and the function's
the last statement 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
******************************************************************* calls a scalar function to allow any place in a T-SQL statement that uses a scalar expression to invoke a return
any function that returns a scalar value (the same as the data type of a scalar expression). Must use at least two parts
the function that makes up the name calls the scalar-valued function, the schema name. The name of the object, such as dbo. Max (12,34).
(2) 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
a value-type function does not have a function body enclosed by a begin-end statement. The table it returns is the one located in the
the SELECT command in the RETURN clause is filtered from the database. Inline table-valued functions are functionally 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 ')
(3) Multi-statement table-valued function
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 a function body enclosed by a begin-end statement like a scalar type function, returning
The data in the returned value table is inserted by a statement in the body of the function. This shows that it can make multiple queries,
multiple filtering and merging of data makes up for 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.
Note: withProgrammingThe functions in the language are different, the SQL Server custom function must have a return
return value.
Note: Schemabinding is used to bind a function to the object it refers to. Once the function is bound,
You cannot delete, modify, unless you delete the binding.