Shulou(Shulou.com)06/01 Report--

There are two kinds of SQL Server user-defined functions: scalar function and table-valued function (inline table-valued function and multi-statement table-valued function).

Beside the question, some friends may not know where SQL Serve user-defined functions should be written. Here is a simple hint: in Microsoft SQL Server Managerment Studio, expand the database that specifically needs to create SQL Server user-defined functions (that is, each user-defined function is only useful for a specific database), then find the programmability option, and then expand to find the function option. In the specific function options, you can refer to the following figure to select the right mouse button to add.

Scalar function

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

It is written as follows:

-Author:-- Create date:-- Description:-- = CREATE FUNCTION (--Add the parameters for the function here) RETURNS ASBEGIN-- Declare the return variable here DECLARE-- Add the T-SQL statements to compute the return value here SELECT =-- Return the result of the function RETURN END

Example:

-- =-- Author:-- Create date:-- 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 @ result=@firstNum+@secondNum-- Return the result of the function RETURN @ resultENDGO

Digression: let's take a look at the above writing. For SQL Server, we declare a variable by using the @ variable name, and relative to programming, the way SQL Server declares makes a joke on us, that is, the variable comes before the type. For the way that you need to pass parameters and you don't need to pass parameters, it's actually the same as the way we program. If there are parameters, they are as follows:

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

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

CREATE FUNCTION GetSum ()

When it comes to the way we return, this is different from the way we program. The return type of the SQL Server function is not placed before the function name, but after the parentheses of the function name. And the return type of the function needs to use the return keyword RETURNS instead of RETURN.

As far as functions are concerned, of course, there will be so-called function bodies. The same goes for scalar functions. Its function body is contained in:

ASBEGIN-- function body END

If you need to declare a variable in the function body, you need to use the DECLARE keyword to declare it. The return inside the function is the keyword RETURN.

Well, the example of the scalar function is over, and to save it to 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 results in the same way as the query table data in the query window.

It's easy to understand how to use it, but it's important to note that the object name [dbo] is not omitted, and there is no less () after the [GetSum] function. Oddly enough, for table-valued functions, the object name [dbo] can be executed correctly without writing.

Select [dbo]. [GetSum] () inline table-valued functions

Compared to scalar functions, only one scalar value is returned, while inline table-valued functions return table data. Of course, the table data is the Table type.

It is written as follows:

-Author:-- Create date:-- Description:-- = CREATE FUNCTION (--Add the parameters for the function here,) RETURNS TABLE ASRETURN (--Add the SELECT statement with parameter references here SELECT 0) GO

Example:

-Author:-- Create date:-- Description:-- = CREATE FUNCTION [GetMoreThanSalary] (@ salary int) RETURNS TABLE ASRETURN (SELECT [FName], [FCity], [FAge], [FSalary] FROM [Demo]. [dbo]. [T _ Person] Where [FSalary] > @ salary) GO

Digression: the content mentioned above in the scalar function will not be repeated 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 inline table-valued functions, the structure of the function body is as follows. The inline table-valued function only executes one SQL statement and returns the Table result.

ASRETURN-- function body END

The table-valued function is executed as follows:

Select [FName], [FCity], [FAge], [FSalary] from [dbo]. [GetMoreThanSalary] (8000)

As you can see, this mode of execution is the same as that of a regular table. A table-valued function is actually equivalent to a virtual table stored in memory space.

Multi-statement table valued function

Both multi-statement table-valued functions and inline table-valued functions are table-valued functions, and the results they return are of type Table. A multi-statement table-valued function, as its name implies, means that data of type Table can be created with multiple statements. Unlike inline table-valued functions, the return result of an inline table-valued function is determined by the SELECT statement in the function body. On the other hand, a multi-statement table-valued function needs to specify the structure of a specific Table type. That is, the returned Table has defined which fields to return. So it can support the execution of multiple statements to create Table data.

It is written as follows:

-Author:-- Create date:-- Description:-- = CREATE FUNCTION (--Add the parameters for the function here,) RETURNS TABLE (--Add the column definitions for the TABLE variable here,) ASBEGIN-- Fill the table variable with the rows for your result set RETURN ENDGO

Example:

-Author:-- Create date:-- Description:-- = ALTER FUNCTION DemoFun () RETURNS @ result TABLE (name nvarchar (20), city nvarchar (20), age int, salary int) ASBEGIN-- Fill the table variable with the rows for your result set insert into @ result (name, city, age, salary) select FName,FCity,FAge,FSalary from dbo.T_Person where FSalary > 8000 insert into @ result (name, city, age, salary) values ('Test' 'China', 1,0) RETURN ENDGO

Digression: it can be seen that the return result of a multi-statement table-valued function is a virtual table that defines a good table structure. This is the same as a scalar function, except that a scalar function returns a type of scalar value. And in the multi-statement table-valued function, you will also find that the last sentence is RETURN. Tell the executor that the multi-statement table-valued function has been executed. The structure of function body is the same as that of scalar function. The way that types are placed after variables does require a change of mind.

RETURNS TABLE (--Add the column definitions for the TABLE variable here,)

The content is not much, but if you want to use it skillfully, you still need to use it more in the project. On the Internet, there are some common custom functions summarized by netizens that you can collect and accumulate, just like doing a project, a good method should form a so-called development library to help us reuse in the next project. Save our development time and improve our work efficiency.

This is the end of this article.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.