SQL custom function function

Https://blog.csdn.net/qq_23833037/article/details/53170789

Https://www.cnblogs.com/youring2/p/4916400.html

User-defined custom functions return scalar values like built-in functions, or you can return result sets as table variables.

The sql function must have a return value.

Ps: a function is treated as a function that handles some data, and because there is a return value, a processed data is needed in the code.

You can directly call the function to process the data and return the data to the code to use.

Scalar function: returns a scalar value.

Table-valued function {inline table-valued function, multi-table-valued function}: returns a rowset (that is, returns multiple values)

The difference between a scalar function and a table-valued function is whether it returns a scalar value (single number or single data) or a table value (multiple data).

1. Scalar function

Create funetion function name (parameter) return returns the value data type [with {Encryption | Schemabinding}] [as] begin SQL statement (must have a return variable or value) End--Schemabinding: bind the function to the object it references (Note: once the function is bound, it cannot be deleted or modified unless the binding is deleted)

Test data:

USE [Scratch] GO/* Object: Table [dbo]. [number] Script Date: 04 Scratch 17:01:31 * / SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGOCREATE TABLE [dbo]. [number] ([number] [int] NULL, [name] [nchar] (10) NULL) ON [PRIMARY] GO

Insert data:

INSERT INTO number (number,name) VALUES (123 recordings'), (222 recollections b'), (333 recordings c'), (323 dudes)

Example:

Alter function SumRes (@ sco nvarchar (20))-- function name and parameter returns real-- return real value type-- real=float (24) asbegindeclare @ sum real declare @ code varchar (11) set @ code = @ sco +'% 'select @ sum = sum (number) from number where name like @ codereturn @ sum-- return value end

Reference a custom function:

(the return value of a user-defined function can be placed in a local variable and assigned with set select exec)

Declare @ sum1 real,@sum2 real,@sum3 realset @ sum1 = dbo.SumRes ('b') select @ sum2 = dbo.SumRes ('b') exec @ sum3 = dbo.sumRes'b'select @ sum1, @ sum2, @ sum3

Example 2:

The following function returns the age based on birthday:

Create function dbo.calcAge (@ birthday datetime)-- function name and parameter returns int-- return value type asbegin declare @ now datetime declare @ age int set @ now=getdate () set @ age=YEAR (@ now)-YEAR (@ birthday) return @ age-- return value endprint dbo.calcAge ('2000-1-1')

Execute this script to create the function, and after the creation is successful, let's call to see the effect: output: 15

2. Table-valued function

A, inline table-valued function

Format:

Create function function name (argument)

Returns table

[with {Encryption | Schemabinding}]

As

Return (a SQL statement)

Example:

Create function tabcmess (@ code nvarchar (50)) returns tableasreturn (select * from number where name = @ code)

Call and result:

B, multi-sentence table-valued function

Definition of multiple table-valued functions: contains multiple SQL statements, must or at least one assign a value to the table variable!

Table variable format:

Returns @ variable name (dt) table (column definition | constraint definition)

Select, insert, update, delete can be executed on table variables

However, the result set of select into and insert statements is inserted from the stored procedure.

Format:

Create function function name (argument)

Return @ dt table (column definition)

[with {Encryption | Schemabinding}]

As

Begin

SQL statement

End

Example:

Create function tabcmess_mul (@ code nvarchar (50)) returns @ dt table (number int,name nchar (10)) asbegininsert into @ dt select number,name from number where name = @ codereturnend

Call and result:

3. Modify custom function

Alter function tabcmess_mul (@ code nvarchar (50)) returns @ dt table (number int,name nchar (10)) asbegininsert into @ dt select number,name from number where name = @ codereturnend4 Delete the custom function drop function tabcmess_mul