SQL Server user-defined functions

1. functions that return scalar data
User-defined functions accept zero or more input parameters and return a single value. Some user-defined functions return a Single Scalar data value, such as an int, Char, or decimal value.

For example, the following statement creates a simple function that returns decimal:

Create Function cubicvolume
-- Input dimensions in centimeters.
(@ Cubelength decimal (4, 1), @ cubewidth decimal (4, 1 ),
@ Cubeheight decimal (4, 1 ))
Returns decimal (12, 3) -- cubic centimeters.
As
Begin
Return (@ cubelength * @ cubewidth * @ cubeheight)
End

Note: There are three differences with stored procedures:
1. The parameters must be enclosed in brackets.
2. There is a return statement next to the parameter, indicating the type of the return value.
3. return is required in the function body.

2. How to call a function

DBO. functionname (parameter1 ,...)

You can then use this function wherever an integer expression is allowed (such as in the calculation column of a table:

Create Table bricks
(
Brickpartnmbr int primary key,
Brickcolor nchar (20 ),
Brickheight decimal (4, 1 ),
Bricklength decimal (4, 1 ),
Brickwidth decimal (4, 1 ),
Brickvolume
(
DBO. cubicvolume (brickheight,
Bricklength, brickwidth)
)
)

The calculation column is the first time we can see it. We thought it was something in a more advanced dataset.

3. functions that return table Variables
In the User-Defined Function that returns the table:

The returns clause defines the local return variable name for the table returned by the function. The returns clause also defines the table format. The local return variable name is within the function.

The Transact-SQL statement in the function body generates a row and inserts it into the return variable defined in the returns clause.

When the return statement is executed, the inserted variable rows are returned in the form of table output in the function. The return statement cannot have parameters.
The result set cannot be directly returned to the user using the transact-SQL statement that returns the table in the function. The unique information returned by the function is the table returned by the function.

In the following example, create a function to return table in the northwind database:

Create Function largeordershippers (@ freightparm money)
Returns @ ordershippertab table
(
Shipperid int,
Shippername nvarchar (80 ),
Orderid int,
Shippeddate datetime,
Freight money
)
As
Begin
Insert @ ordershippertab
Select S. shipperid, S. companyName,
O. orderid, O. shippeddate, O. Freight
From shippers as s inner join orders as O
On S. shipperid = O. shipvia
Where o. Freight> @ freightparm
Return
End

In this function, the returned local variable name is @ ordershippertab. The statement in the function body inserts the row variable @ ordershippertab to generate the table results returned by the function.

The following query references the table returned by the function in its from clause:

Select *
From largeordershippers ($500)

Note that temporary tables cannot be used in functions, but table variables can be used.
The return clause in the function header also defines the table variables to be returned, but the format is special.
The general format of table variables defined in the function body is:
Declare @ tablevar table (
Colname type,
...
)

Differences between temporary tables and table variables:

Temporary tables can be divided into local and global tables. The names of local temporary tables are prefixed with "#" and are only visible in local user connections, the user is deleted when the instance is disconnected. The name of the global temporary table is prefixed with "#". After the table is created, it is visible to all users. When all users that reference the table are disconnected, the table is deleted.

The syntax for creating table variables is similar to that for a temporary table. The difference is that you must name the table variables when creating them. Table variables are one type of variables. Table variables are also divided into local and global variables. The names of local table variables are prefixed, only the local user connection can be accessed. The names of global table variables are prefixed with "@". Generally, they are system global variables. For example, @ error indicates the error number, @ rowcount indicates the number of affected rows.

You can select, insert, update, and delete SQL statements to compare temporary tables and table variables. Their differences are mainly reflected in the following:

1) Table variables are stored in the memory. When you access table variables, SQL server does not generate logs, but logs are generated in temporary tables;

2) Non-clustered indexes are not allowed in Table variables;

3) Table variables cannot have default values or constraints;

4) The statistical information on the temporary table is sound and reliable, but the statistical information on the table variables is unreliable;

5) The temporary table has a lock mechanism, but there is no lock mechanism in the table variables.

Temporary tables cannot be supported in functions because the function cannot make permanent changes to the resource status outside the function scope, which is also called sideeffect in sqlserver ). However, it is not recommended to use a large temporary result set in a function, because placing such a function in a query may cause obvious performance problems, therefore, Batch scripts such as stored procedures are generally used in this case.

The reason why dynamic scripts do not support table variables is that the stored procedure does not accept table-type parameters. However, if the declaration and value assignment of table variables are in the sp_executesql parameter, sp_executesql can be executed, because the table variables are stored in the stmt parameter of sp_executesql and do not need to be passed in, for example, the following code: (of course, this is not much practical)

Declare @ M nvarchar (max)

Set @ M = N "declare @ t table (id int); insert into @ tvalues (1); select * From @ t"

Exec sp_executesql @ M