SQL Server Custom Functions

Brief introduction

There are three types of SQL Server custom functions: Scalar functions (scalar function), inline table-valued functions (inline function), multi-statement table-valued functions (multi-statement function)

Scalar functions: Scalar functions return a scalar value of the type that is determined, and the return value type is a data type other than text, NTEXT, IMAGE, CURSOR, timestamp, and table type. The function body statement is defined within the Begin-end statement, where 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

Inline table-valued functions: inline table-valued functions return a return value as 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

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.

Scalar functions

syntax for creating scalar functions:

Create function [owner of function]. function name (scalar parameter [as] scalar parameter type [= default value]) returns scalar return value type begin     function body (i.e. Transact-SQL statement)      return variable/scalar expression end

Case: Split the string ' 001.002.003.004 ' by the specified delimiter, returning the number of splits

 create FUNCTION dbo. Fun_getstrlistleng (@originlStr varchar (500),--the string to be split @splitvarchar (10)--delimiter) returns int  as  begindeclare @location int ,-- Defines the start position @start int ,--definition @length int  ;--Define variables to receive the number of calculated elements set  @originlStr =ltrim (RTrim (@originlStr))--remove left and right spaces Span style= "color: #0000ff;" >set  @location =charindex (@split, @originlStr)-the position where the split symbol first appears in the string (the index counts from 1)  @length =1while  @location <>0begin set  @[email protected]+1set  @ Location=charindex (@split, @originlStr, @start) set  @[email protected]+ 1endreturn  @lengthend 

To execute a user-defined scalar function:

SELECT dbo. Fun_getstrlistleng (' 001.002.003.004.005 ', '. ')--return 5

The function owner is specified when the function is created, and the owner of the function must be specified when the call is made. (usually dbo)

If you want to use a default value when calling a custom function without passing in a parameter, you must use the Defaults keyword. If the parameter of the custom function does not have a default value, then NULL is returned.

Inline table-valued functions

syntax for creating an inline table-valued Function:

Create function [owner of function]. function name (scalar parameter [as] scalar parameter type [= default value]) returns table [with {encryption | Schemabinding}] [asreturn(a single SELECT statement that determines the data of the returned table. )

Case: Query the student's elective course (including number, name, course number and score), then call the function to inquire about a student's course selection

Char (5)) Returns tablereturn(    select Student.sno,student.sname,course.cno,score.degree from    student, Course,score    where Student.sno=score.sno and Score.cno=course.cno and    [email protected] school number)

Call Syntax:

SELECT * FROM dbo. Fun_getlist (' 2001 ')

Multi-statement table-valued functions

Syntax for creating a multi-statement function:

Create function [owner of function]. function name (scalar parameter [as] scalar parameter type [= default]) returns @ table variable table table definition (that is, column definition and constraints) BEGIN     function body (i.e. Transact -sql statement)     return end

Case: Splits the string ' 001.002.003.004 ' by the specified delimiter and returns

Alter FUNCTION [dbo]. [Fun_splitstr] (     @originalStr      varchar (8000),--the string to be split     @split VARCHAR ()  --delimited) RETURNS @temp TABLE (Result varchar (+) as BEGIN     DECLARE @result as VARCHAR (+);   --Define a variable to receive a single result     SET @originalStr = @originalStr + @split;     while (@originalStr <> ")     BEGIN         SET @result = Left (@originalStr, CHARINDEX (@split, @originalStr)-1); C10/>insert @temp VALUES (@result);         The--stuff () function deletes characters of a specified length and can insert another set of characters at the specified starting point.         SET @originalStr = STUFF (@originalStr, 1, CHARINDEX (@split, @originalStr), ");     End     RETURN End

Called in the same way as inline functions

Scope of application

1. Query only, do not modify the status of the database (modify, delete records in the table, etc.)

2. When the result set needs to be obtained by recursive methods, functions can be used, the function is more flexible

3. A function can be used when the result set needs to be referenced directly. The result set needs to be re-processed (referred to as medium in the SELECT statement), functions can be used, and functions can be embedded in SQL statements such as SELECT.

Precautions:

User-defined functions cannot be used to perform a series of operations that alter the state of a database

You need to be aware of when writing custom functions:

For scalar functions:

1. Must be added before all the entry parameters

2. Return after create, word is returns, not return

3. Returns the following is not a variable, but the type of return value, such as: Int,char, etc.

4. In the Begin/end statement block, return.

Inline table-valued functions:

1. Can only return table, so returns must be table behind

2. As is not begin/end, there is only one return statement to return a specific record.

Multi-statement table-valued functions:

1. Returns directly after the table type is defined, the first is to define the table name, indicating that the previous to add @, then the keyword table, and finally the structure of the table.

2. In the Begin/end statement block, insert the result that needs to be returned directly into the table defined by returns, and return the result when the last return.

3. Finally only need to return,return the back not with any variables.

Reference:

Https://www.cnblogs.com/Brambling/p/6686947.html

SQL Server Custom Functions