SQL Server user-defined functions

Custom Functions

 

A user-defined custom function returns a scalar value like a built-in function. You can also use table variables to return the result set.

User-Defined Function Type:

Scalar functions:Returns a scalar value.

Table value function {Inline Table value functions, multi-Table value functions }:Returns a row set (that is, multiple values)

1, Scalar function

Create FunctionFunction Name (parameter)

ReturnsReturn Value Data Type

[With {encryption | schemabinding}]

[As]

Begin

SQLStatement (Must have returnVariable or value)

End

Schemabinding:Bind a function to the object it references. (Note: Once a function is bound, it cannot be deleted or modified unless it is bound)

Create Function avgresult (@ scode varchar (10 ))

Returns real

As

Begin

Declare @ AVG real

Declare @ code varchar (11)

Set @ code = @ scode + '%'

Select @ AVG = AVG (result) from learnresult_1_jiali

Where scode like @ code

Return @ AVG

End

Execute User-Defined Functions

SelectUser name. Function nameField alias

Select DBO. avgresult ('s0002') as result

User-Defined Function return values can be placed in local variables, using set, select, ExecAssignment

Declare @ avg1 real, @ avg2 real, @ avg3 real

Select @ avg1 = DBO. avgresult ('s0002 ')

Set @ avg2 = DBO. avgresult ('s0002 ')

Exec @ avg3 = DBO. avgresult 's0002'

Select @ avg1 as avg1, @ avg2 as avg2, @ avg3 as avg3

Function reference

Create Function Code (@ scode varchar (10 ))

Returns varchar (10)

As

Begin

Declare @ cCode varchar (10)

Set @ scode = @ scode + '%'

Select @ cCode = cCode from cmessage

Where cCode like @ scode

Return @ cCode

End

Select name from class where cCode = DBO. Code ('c001 ')

2Table value functions

A,Inline Table value functions

Format:

Create FunctionFunction Name (parameter)

Returns table

[With {encryption | schemabinding}]

As

Return (One SQL statementStatement)

Create Function tabcmess (@ code varchar (10 ))

Returns table

As

Return (select cCode, scode from cmessage where cCode like @ cCode)

B,Multi-sentence Table value functions

Create FunctionFunction Name (parameter)

ReturnsTable variable name table (Table variable definition)

[With {encryption | schemabinding}]

As

Begin

SQLStatement

End

The multi-sentence Table value function contains multiple SQL statements.Statement, with at least one data value filled in the table variable

Table variable format

Returns @Variable name table (ColumnDefinition |Constraint Definition [,…])

Select, insert, update, and delete rows in Table variables can be executed.,However, selectAnd insertThe statement result set is inserted from the stored procedure.

Create Function tabcmessalot (@ code varchar (10 ))

Returns @ ctable table (Code varchar (10) null, cname varchar (100) null)

As

Begin

Insert @ ctable

Select cCode, explain from cmessage

Where scode like @ code

Return

End

Select * From tabcmessalot ('s0003 ')

Convert stored procedures into Functions