SQL Server user Custom functions

In SQL Server, users can not only use standard built-in functions, but also use their own defined functions to implement some special functions. Can be created using the Create FUNCTION statement. It is important to note that the function name must be unique in the database, can have parameters, can have no parameters, and its parameters can only be input parameters, with a maximum of 1024 parameters.

A user-defined function cannot be used to perform a series of operations that alter the state of a database, but it can be used like a system function in a program segment such as a query or stored procedure, or it can be executed as a stored procedure through an execute command.

There are three kinds of custom functions, including scalar function, table-valued function and multi-statement table-valued function.

(1) Scalar function: For a single value operation, returns a single value. You can use scalar functions wherever you can use an expression.

(2) Table-valued Function: The return value is a collection of records--a table. In this function, the return statement contains a separate SELECT statement.

(3) Multi-statement table-valued Function: The return value is a recordset composed of selected results.

One: scalar function

The format is as follows:

CREATE FUNCTION [owner_name.] Function_name

([{@parameter_name [as] scalar_parameter_data_type [= default]} [,... N]])

RETURNS Scalar_return_data_type

[As]

BEGIN

Function_body

RETURN scalar_expression

END

The explanations are as follows:

(1) Function_name: Refers to the name of the user-defined function. Its name must conform to the naming convention for identifiers, and for its owner, the name must be unique in the database.

(2) @parameter_name: Parameters of the user-defined function. A parameter can be used instead of a constant instead of a table name, column name, or other database object name. The value of each declared parameter must be specified by the user when the function executes, unless the default value for the parameter is already defined. If the parameter of the function has a default value, you must specify the "default" keyword to get the default value when calling the function.

(3) Scalar_parameter_data_type: The data type of the parameter. can be any scalar data type supported by SQL Server (except text, ntext, image, and timestamp).

(4) Scalar_return_data_type: is the return value of the user-defined function. The data type is the same as the requirement for the (3) input parameter. Cannot have parameters of text, ntext, image, and timestamp types.

(5) Function_body: A series of Transact-SQL statements between Begin and end, which are used only for scalar functions and multi-statement table-valued functions.

(6) Scalar_expression: An expression that returns a value from a user-defined function.

If you feel complex, you can simplify it to

CREATE function function_name (function name)

(@ Parameter 1 parameter 1 data type [= default], @ Parameter 2 parameter 2 data type [= Default],......)

RETURNS Return Data type

[As]

BEGIN

SQL statements

Return scalar_expression (returned value)

END

Example: Create a user-defined function in the Student library Xuefen, the function by entering the score to determine whether to obtain the credit, when the score is greater than or equal to 50 o'clock, return to earn credits, otherwise, return to not get credit. The code is as follows:

CREATE function Xuefen (@inputxf int)--(1) The function name is Xuefen, the input parameter is @inputxf, the parameter data type is int

RETURNS nvarchar (10)--(2) The return value type of the function is nvarchar (10)

Begin--(3) Place the SQL statement between BEGIN and END, making it a whole.

declare @retrunstr nvarchar (10)--(4) The variable that declares the return value of the function is @retrunstr, the data type is nvarchar (10), and the type is consistent with the declaration in (2)

If @inputxf >=50

Set @retrunstr = ' earn credits '

Else

Set @retrunstr = ' not earned credit '

Return @retrunstr

END

The code for using the function is as follows:

SELECT study number, score, Dbo.xuefen (score)--to indicate the owner of the function in use, in this case, the dbo must be added.

As credit status from course Registration WHERE Course Number = ' 0003 '

GO

Two: Table-valued functions:

Table-valued functions follow the principles:

1. The returns clause contains only the keyword table. You do not have to define the format of the return variable because it is formatted by the result set of the SELECT statement in the return clause.

2. Function_body is not delimited by begin and end.

3. The return clause contains a single SELECT statement in parentheses. The result set of the SELECT statement forms the table returned by the function. The SELECT statement used in the inline table-valued function is subject to the same restrictions as the SELECT statement used in the view.

Example: Create an inline table-valued function Xuesheng in the student library that returns the basic information of the student based on the input department code. The code is as follows:

CREATE FUNCTION Xuesheng (@inputxbdm nvarchar (4)) RETURNS table

As

RETURN

(SELECT number, name, admission time from student WHERE Department code [email protected])

GO

Once you have established the inline table-valued function, you can use it as you would with a table or view:

SELECT * from DBO. Xuesheng (' 01 ')

GO

Three, multi-statement table-valued functions

Example: Create a multi-statement table-valued function Chengji in the student library, which returns the names and grades of the students taking the course based on the name of the course you enter. The code is as follows:

CREATE FUNCTION Chengji (@inputkc as char (20))

RETURNS table variable @chji table [email protected]

(Course name char (20), name char (8), score tinyint)

As

BEGIN

INSERT @chji

SELECT C. Course name, S. Name, K. Score from student as S INNER join course Register as K on S. =k. INNER JOIN Course as C on c. Course number =k. Course number WHERE C. Course Name C0>[email protected]

Return-Returns the data for the table variable directly, using return alone

END

GO

Enter the following query command in Query Analyzer:

SELECT * from DBO. Chengjii (' College Chinese ')

Summarize:

The following statements are allowed in the body of a multi-statement function.

1, assignment statement.

2, control flow statement.

3. The DECLARE statement, which defines the data variables and cursors for the function Local.

4, the SELECT statement, which contains a selection list with an expression that assigns the value to the local variable of the function.

5. A cursor operation that references a local cursor declared, opened, closed, and disposed in a function. Only fetch statements that use the INTO clause to assign values to local variables are allowed, and FETCH statements that return data to the client are not allowed.

6, INSERT, UPDATE, and DELETE statements that modify the function's local table variable.

7. The EXECUTE statement invokes the extended stored procedure.

SQL Server user Custom functions