13. SQL Server Custom Functions

SQL Server Custom Functions

Not only can you use system functions (such as aggregate functions, String functions, time-date functions, and so on) in SQL Server, you can also customize functions as needed.

Custom functions are divided into scalar-valued functions and table-valued functions.

Where a scalar-valued function returns a single value, and a table-valued function is used to return a result set.

function parameters

A parameter can be a constant, a column in a table, an expression, or another type of value. There are three types of parameters in the function.

1. Input: Indicates that a value must be entered.

2. Optional value: When you execute this parameter, you can choose not to enter parameters.

3, the default value: The function has a default value exists, the call can not specify the value.

Creating scalar-valued functions

Grammar:

Create function function name (parameter) Returns return value data type [with{encryption | Schemabinding}][as]begin  SQL statement (must have return variable or value) End Schemabinding: Binds a function to the object it refers to (note: Once a function is bound, it cannot be deleted, modified, unless the binding is removed)

Example:

Drop functionDbo.input--Delete a functionGoCreate functionDbo.input--defines the function schema. Method name(@num1 int,--Input Parameters@num2 int = NULL,--Optional Parameters@oper varchar = '+' --Default Parameters)returns int asbegin    Declare @sum int    if(@oper='+')    begin        Set @sum = @num1 + @num2    End    Else    begin        Set @sum = 0    End    return @sumEndGoSelectDbo.input (1,NULL,default)--Parameter 1 is required, parameter 2 optional, parameter 3 defaultSelectDbo.input (1,2,default)--Output 3SelectDbo.input (1,2,'*')--Output 0 * not judged

Custom functions can place values in local variables and assign values with set select exec

Declare @number intSelect @number =Dbo.input (1,2,default)Print @numberDeclare @set intSet @set =Dbo.input (1,2,default)Print @setDeclare @exec intexec @exec =Dbo.input1,2,'+'Print @exec

Referencing a function in a query

Create TableTest (IDint Identity(1,1), namevarchar(Ten), BirthDaydatetime)Insert  intoTestValues('Zhang San','1998-02-01'),('John Doe','1981-10-1'),('Harry','1985-5-2')Select *  fromTest--Test Information

After the function is created and executed

Create functionDbo.getage (@birthDay datetime)returns int asbeginDeclare @age intSet @age = DateDiff(YY,@birthDay,getdate())return @ageEndSelectName asName, Dbo.getage (BirthDay) asAge fromTest

Note: Scalar-valued functions cannot return data for text (text, ntext), images, cursors, or timestamp types, and cannot be used to modify database state.

Using a function in a SELECT statement can have a negative effect, because each return row invokes the function one time. Therefore, you should avoid using complex functions when returning large datasets.

Table-Valued functions

Table-valued functions consist of two types: inline and multi-statement functions.

An inline table-valued function returns only one result set, and a multi-statement function can contain some control logic in the body of the function.

1. Inline table-valued functions

Grammar:

Create function function name (parameter) returns Table [with{encryption | Schemabinding}] as
return (One SQL statement)

Example:

--CreateCreate functionGetdetails (@id int)returns Table asreturn(Select *  fromStudentwhereId= @id)--returns after executing a statement--calledSelect *  fromDbo.getdetails (Ten)

2. Multi-statement function

Multi-statement functions can create temporary tables with multiple statements, which fields are required, and which data are required to be added to the staging table.

Grammar:

Create function function name (parameter) returns table variable name table (table variable definition) [with{encryption | Schemabinding}]asbegin    SQL statement end

Example:

Create functiondbo. Test ()returns @temp Table(Namevarchar( -), SexChar(2), ageint) asbeginInsert  into @temp(Name,sex,age)Values('Multi-statement','.', -)Insert  into @temp(Name,sex,age)SelectName,sex,age fromStudentwhereAge>  -return End

As you can see, the result of a multi-statement function is a virtual table with a well-defined table structure, and finally a return to tell the SQL that multiple statements have been executed. No writing will return.

13. SQL Server Custom Functions