The difference between stored procedures and functions in SQL Server

There is essentially no difference. It's just that the function has the restriction that it can only return one variable. The stored procedure can return multiple. Functions can be embedded in SQL and can be called in select, but stored procedures cannot. The essence of execution is the same.

　There are many restrictions on functions. For example, temporary tables cannot be used, only table variables can be used. Some functions are not available, etc. However, the restrictions on stored procedures are relatively few

　1. Generally speaking, stored procedures implement The function is a little more complicated, and the function implementation is more targeted.

　2. Stored procedures can return parameters, while functions can only return values ​​or table objects.

　　3. Stored procedures are generally executed as an independent part, while functions can be called as part of a query statement. Since the function can return a table object, it can be located after the FROM keyword in the query statement.

　　4. When stored procedures and functions are executed, SQL Manager will go to the procedure cache to get the corresponding query statement. If there is no corresponding query statement in the procedure cache, SQL Manager will compile the stored procedures and functions. .

　The execution plan is saved in the procedure cache. After it is compiled, the execution plan in the procedure cache is executed. Afterwards, SQL SERVER will consider whether to save this plan in the cache based on the actual situation of each execution plan. , the first criterion for evaluation is the frequency with which this execution plan may be used; the second is the cost of generating this plan, that is, the time it takes to compile. The plan saved in the cache does not need to be compiled the next time it is executed.

　 The specific difference between stored procedures and user-defined functions

Let’s look at the definition first:

Stored procedures

Stored procedures can make it much easier to manage the database and display information about the database and its users. A stored procedure is a precompiled collection of SQL statements and optional control flow statements that are stored under a name and processed as a unit. Stored procedures are stored in the database and can be executed by the application through a call, and allow users to declare variables, conditional execution, and other powerful programming features.

　 Stored procedures can include program flow, logic, and queries to the database. They can accept parameters, output parameters, return single or multiple result sets, and return values.

　 Stored procedures can be used for any purpose of using SQL statements, and they have the following advantages:

　 A series of SQL statements can be executed in a single stored procedure.

　 You can reference other stored procedures from within your own stored procedures, which can simplify a series of complex statements.

　 Stored procedures are compiled on the server when they are created, so they execute faster than a single SQL statement.

　User-Defined Function

　A function is a subroutine composed of one or more Transact-SQL statements that can be used to encapsulate code for reuse. Microsoft® SQL Server® 2000 does not limit users to the built-in functions defined as part of the Transact-SQL language, but instead allows users to create their own user-defined functions.

　 User-defined functions can be created using the CREATE FUNCTION statement, modified using the ALTER FUNCTION statement, and removed using the DROP FUNCTION statement. Each fully legal user-defined function name (database_name.owner_name.function_name) must be unique.

　Must be granted CREATE FUNCTION permission to create, modify or remove user-defined functions. Before a user who is not the owner can use a function in a Transact-SQL statement, the user must be granted the appropriate permissions on the function. To create or alter a table that references a user-defined function in a CHECK constraint, DEFAULT clause, or calculated column definition, you must also have REFERENCES permission on the function.