Let's talk about SQL SERVER custom functions ~

When using SQL SERVER database, functions should be used, such as the system aggregate function Sum (), Max () and so on. But when some beginners write custom functions, they often ask what a table-valued function is, and what a scalar-valued function is.

A table-valued function, as the name implies, returns a table for the return value of the function. is the TABLE type. The table type is equivalent to a single virtual table stored in memory.

Table-valued functions are divided into inline table-valued functions (which are equivalent to parameterized views) and multi-statement table-valued functions, which, like the same principle, have slightly different functions.

Multi-statement table-valued functions

Syntax templates:

CREATE function < functions name >
(
--Parameters of the function (can be multiple)
< @param1, sysname, @p1 > <data_type_for_param1,, Int>
< @param2, sysname, @p2 > <data_type_for_param2, char>
)
RETURNS
<@ Variable name > table
(
--The Field field name type in the returned virtual table
<column_1, sysname, c1> <data_type_for_column1, Int>
<column_2, sysname, c2> <data_type_for_column2, int>
)
As
BEGIN
--function body, which can define a number of variables, in short, the final return should be a result set
RETURN
END

GO

To illustrate:

CREATE FUNCTION Tvpoint
(
@x INT,
@y INT
)
RETURNS
@Points TABLE
(
X INT,
Y INT
)
As
BEGIN
INSERT into @Points (x, y) VALUES (@x,@y)
RETURN
END
GO

Call: SELECT * from Tvpoint (10,20);

Inline table-valued functions

Syntax templates:

CREATE function < functions name >
(--Parameters of the function (can be multiple)
< @param1, sysname, @p1 > <data_type_for_param1,, Int>
< @param2, sysname, @p2 > <data_type_for_param2, char>
)
RETURNS TABLE
As
RETURN
(
--A SQL statement that returns a result set
SELECT 0
)
GO

To illustrate:

CREATE FUNCTION Sales.ufn_customernamesinregion (
@Region nvarchar (50)
)
RETURNS table
As
RETURN
(
--in fact, an SQL statement is, of course, a result set returned from a parameter
SELECT DISTINCT s.name as Store, a.city
From Sales.Store as S
JOIN Sales.customeraddress as CA on CA. CustomerID = S.customerid
JOIN person.address as A on a.addressid = CA. Addressid
JOIN person.stateprovince SP on
Sp. StateProvinceID = A.stateprovinceid
WHERE SP. Name = @Region
);
GO

Call: SELECT * from Sales.ufn_customernamesinregion (N ' Washington ');

Thus, the difference between an inline table-valued function and a multi-statement table-valued function is:

Inline table-valued function, the RETURNS clause contains only the keyword table. You do not have to define the format of the return variable because it is formatted by the result set of the SELECT statement in the return clause.

The function body of an inline table-valued function is not separated by BEGIN and END.

The RETURN clause of an inline table-valued function contains a single SELECT statement in parentheses. The result set of the SELECT statement forms the table returned by the function. The SELECT statement that it uses is subject to the same restrictions as the SELECT statement used in the view.

Scalar-valued functions: The simple point is, of course, a function that returns a type value.

Syntax templates:

CREATE function < functions name >
(
--The receive parameter of the function
< @Param1, sysname, @p1 > <data_type_for_param1, int>
)
RETURNS < type of return value >
As
BEGIN
--You can define some variables (you can also return a variable, the type of the variable returned is the same as the return type of the function)
DECLARE < @ResultVar, sysname, @Result > <function_data_type,,int>

--Some SQL statements, in plain language, are a series of operations that write code to assign values to variables to be returned
SELECT < @ResultVar, sysname, @Result > = < @Param1, sysname, @p1 >

--Return value
RETURN < @ResultVar, sysname, @Result >

END
GO

To illustrate:

(The simplest hellworld)

CREATE FUNCTION F_helloword (@Str VARCHAR (20))

RETURNS VARCHAR (20)

BEGIN

RETURN @Str

END

Call:

DECLARE @Ret VARCHAR (20)

EXECUTE @Ret =f_helloword (' Hello World ')

PRINT @Ret

All of these are simple instructions to SQL SERVER custom functions, the actual programming work, to be more complex!

Let's talk about SQL SERVER custom functions ~