SQL user-defined functions

For SQL Server user-defined functions, there are scalar functions, table-valued functions (inline table-valued functions, multi-statement table-valued functions).

aside, there may be some friends who do not know the SQL serve user-defined function should be written where, here is a simple hint, in Microsoft SQL Server managerment Studio, expand the specific need to create SQL The database of server user-defined functions (that is, each user-defined function is useful only for a specific database), then find the programmability options, then expand the Find function options, in the specific function options can be referenced in the way the right mouse button selection to add.

Scalar functions

The so-called scalar function simply means that the result of the return is just a scalar, and for me, the result of the return is a value of a type.

The wording is as follows:

--=============================================--Author:        <author,,name>--Create Date: <create date, >--Description:    <description, >--=============================================create FUNCTION < scalar_function_name, sysname, functionname> (    --ADD The parameters for the Function here    < @Param1, Sysnam E, @p1 > <data_type_for_param1, int>) RETURNS <function_data_type, Int>asbegin    -Declare the Return variable here    DECLARE < @ResultVar, sysname, @Result > <function_data_type,,int>    --Add the T-SQL statements to compute the return value here    SELECT < @ResultVar, sysname, @Result > = < @Param1, sysname, @p1 >-    return the result of    the function Return < @ResultVar, sysname, @Result >end

Example:

--=============================================--Author:        <author,,name>--Create Date: <create date, >--Description:    <description,, >--=============================================create FUNCTION Getsum
(    @firstNum int,    @secondNum int) RETURNS intasbegin    --Declare The return variable here    Declare @result  int    --ADD the T-SQL statements to compute the return value here    SELECT @[email protected][email protected]    -- Return the result of the function    return @resultENDGO

digression : Let's take a look at the notation above, for SQL Server, we declare a variable by using the @ variable name , and as opposed to programming, the way SQL Server declares is a joke to us, Is the first variable followed by the type. The way that you need to pass a parameter and not need to pass the argument is the same as the way we program it. The parameters are as follows:

CREATE FUNCTION getsum (    @firstNum int,    @secondNum int)

If there are no parameters, you can just keep the parentheses. It is consistent with the function we understand.

CREATE FUNCTION getsum ()

For the return method, this is not the same as the way we programmed it. The return type of a SQL Server function is not placed in front of the function name, but behind the function name brackets. And the return type of the function needs to return the keyword RETURNS, not return.

For a function, of course, there is also the so-called function body. Scalar functions are the same. Its function body is contained in:

Asbegin    --function body End

For variables that need to be declared within the body of the function, they need to be declared using the DECLARE keyword. Returns in the function body are the keyword return.

Well, the example of a scalar function is also done, and to be stored in the database, you also need to click on the Execute action in the Microsoft SQL Server Management Studio tool. After that, you can query the query window in the same way as the query table data for the results.

Use the understood, but be aware that the [dbo] object name cannot be saved, and the [getsum] function can be followed by less (). Oddly enough, the object name [dbo] does not write or execute correctly for table-valued functions.

SELECT [dbo]. [Getsum] ()

Inline table-valued functions

Only a scalar value is returned relative to the scalar function, and the inline table-valued function returns the table data. Of course, the table data is of the type table.

The wording is as follows:

--=============================================--Author:        <author,,name>--Create Date: <create date, >--Description:    <description,,>--=============================================create FUNCTION < inline_function_name, sysname, functionname> (        --ADD The parameters for the Function here    < @param1, Sysnam E, @p1 > <data_type_for_param1,, Int>,     < @param2, sysname, @p2 > <data_type_for_param2,, char>) RETURNS TABLE Asreturn (    --ADD The SELECT statement with parameter references here    Select 0) GO

Example:

--=============================================--Author:        <author,,name>--Create Date: <create date, >--Description:    <description,,>--=============================================create FUNCTION [ Getmorethansalary] (        @salary int) RETURNS TABLE asreturn (    SELECT [fname],[fcity],[fage],[fsalary] from [demo].[ DBO]. [T_person] Where [fsalary] > @salary) GO

Off- topic: scalar functions mentioned above are not duplicated here. The table structure returned by the inline table function is determined by the SELECT statement in the function body.

For scalar functions, the function body is contained in the following structure.

Asbegin    --function body End

But for the inline table-valued function, the structure of the function body is the following way. The inline table-valued function returns a table result after executing only one SQL statement.

Asreturn    --function body End

Table-valued functions are executed in the following way:

SELECT [Fname],[fcity],[fage],[fsalary] FROM [dbo]. [Getmorethansalary] (8000)

It can be seen that this is done the same way as normal tables do. A table-valued function is actually equivalent to a virtual table stored in a memory space.

Multi-statement table-valued functions

A multi-statement table-valued function followed by an inline table-valued function is a table-valued function, and they return the result as a table type. A multi-statement table-valued function, as its name implies, is the ability to create table-type data through multiple statements. Unlike inline table-valued functions, the return result of an inline table-valued function is determined by the SELECT statement in the body of the function. A multi-statement table-valued function is a structure that needs to specify a specific table type. That is, the returned table, which has been defined for which fields to return. So it can support the execution of multiple statements to create table data.

The wording is as follows:

--=============================================--Author:        <author,,name>--Create Date: <create date, >--Description:    <description,,>--=============================================create FUNCTION < table_function_name, sysname, functionname> (    --ADD The parameters for the Function here    < @param1, sysname , @p1 > <data_type_for_param1,, Int>,     < @param2, sysname, @p2 > <data_type_for_param2,, char>)  RETURNS < @Table_Variable_Name, sysname, @Table_Var > table (    --ADD the column definitions for the table Variable Here    <column_1, sysname, c1> <data_type_for_column1,, Int>,     <column_2, sysname, c2> < Data_type_for_column2, int>) asbegin    --Fill The table variable with the rows for your result set        RETURN Endgo

Example:

--=============================================--Author:        <author,,name>--Create Date: <create date, >--Description:    <description,,>--=============================================alter FUNCTION Demofun () RETURNS @result TABLE (    name nvarchar), City    nvarchar (a), age    int,    salary int) Asbegin    --Fill The table variable with the rows for your result set    insert to @result (name, city, age, salary)    SE Lect fname,fcity,fage,fsalary from dbo. T_person where fsalary>8000    insert into @result values    (' Test ', ' China ', 1, 0)    RETURN Endgo

Off- topic: It can be seen that the return result of a multi-statement table-valued function is a virtual table that defines a table structure. This is the same as a scalar function, except that a scalar function returns a scalar value of a type. And in the multi-statement table-valued function, you will also find that the last sentence is RETURN. Tells the execution program that the multi-statement table-valued function has been completed. The function body structure is the same as the structure of a scalar function. It really takes a good idea to change the way a type is placed behind a variable.

RETURNS < @Table_Variable_Name, sysname, @Table_Var > table (    --ADD the column definitions for the table Variable Here    <column_1, sysname, c1> <data_type_for_column1,, Int>,     <column_2, sysname, c2> < Data_type_for_column2, int>)

Content is not much, but to be proficient in use, or need to use more in the project. Online Some users summed up the commonly used custom functions you can collect accumulation, like doing projects, a good way to form a so-called development library, to help us in the next project reuse. Save our development time and improve our efficiency.

This is the end of this article.

SQL user-defined functions