Interview questions-the difference between stored procedures and functions in SQL

There is no intrinsic difference between stored procedures and functions in SQL

Functions, only one variable can be returned.  Functions can be embedded in SQL and can be called in a select, and stored procedures do not work. But functions also have more restrictions, such as the inability to use temporary tables

Stored procedure, can return multiple variables.

the stored procedure is defined as follows:

Stored procedures can make it much easier to manage the database and to display information about the database and its users. A stored procedure is a precompiled collection of SQL statements and optional control-flow statements, stored as a single 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 the user to declare variables, conditional execution, and other powerful programming features. Stored procedures can contain program flow, logic, and queries against the database. They can accept parameters, output parameters, return single or multiple result sets, and return values.

You can use stored procedures for any purpose that uses SQL statements, with the following characteristics:

1. You can execute a series of statements in a single stored procedure

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

3. The stored procedure is compiled on the server when it is created, so it executes faster than a single SQL statement.

User-defined Functions

As with any function, a user-defined function is a routine that can return a value. Each user-defined function can be divided into 3 types depending on the type of value returned:

1. Functions that return updatable data tables
If a user-defined function contains a single SELECT statement and the statement can be updated, the table formatting results returned by the function can also be updated.
2. Functions that return non-updatable data tables
If a user-defined function contains more than one SELECT statement, or contains a SELECT statement that is not updatable, the table formatting results returned by the function are not updatable.
3. Functions that return scalar values
A user-defined function can return a scalar value.

Difference between the two

1. In general, the function of the stored procedure implementation is a bit more complex, and the function implementation of the function is relatively strong.

2. Parameters can be returned for stored procedures, and functions can only return values or table objects.

3. The stored procedure is typically performed as a separate part, and the function can be called as part of a query statement, since the function can return a Table object, so it can be located after the FROM keyword in the query statement.

4. When the stored procedure and function are executed, SQL Manager will go to the procedure cache to fetch the corresponding query statement, and if there is no corresponding query in the procedure cache, SQL Manager compiles the stored procedures and functions.

The Procedure cache holds the execution plan (execution plan), executes the execution plan in the Procedure cache when it is compiled, and then SQL Server will follow each execution Plan's actual situation to consider whether or not to save the plan in the cache, the criteria for judging is the frequency at which the execution plan might be used, and secondly, the cost of generating the plan, which is the time it takes to compile. The plan saved in the cache will not be compiled the next time it executes.

Interview questions-the difference between stored procedures and functions in SQL