13. SQL Server Custom Functions

Source: Internet
Author: User
Tags scalar

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

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.