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