Reprint SQL stored procedure and function difference

SQL Server user-defined functions and stored procedures have similar functionality, and you can create bundled SQL statements that are stored in the server for later use. This can greatly improve productivity by reducing the time required for programming by doing the following:

• Reuse programming code to reduce programming development time.
• Hide SQL details, leave SQL tedious work to database developers, and program developers focus on high-level programming languages.
• Maintenance centralization allows you to make logical changes to your business in one place, and then make these changes automatically applied to all relevant programs.

At first glance, the functionality of user-defined functions and stored procedures seems to be the same. However, there are some subtle but important differences between the two:

• The stored procedure is called independently using the EXEC command, and the user-defined function is called in another SQL statement.
• Stored programs allow users and programs to use stored procedures instead of allowing them to access tables, which enhances program security. Storage programs restrict user action permissions more granular than standard SQL Server. For example, if you have a deposit form, every time you sell a cargo, the cashier will update the form (minus one item from the stock). You can set permissions on the cashier to allow them to use the Decrement_item stored procedure, rather than allowing them to have any permission to modify or village forms.
• The function must always return a value (a scalar value or a table). Stored procedures can return a scalar value, a table value, or no return value.

A user-defined function is a procedure that has a return value, and the return value of the table is to be handled by a table variable.

Stored procedures:

They can accept parameters, output parameters, return single or multiple result sets, and return values

1. You can execute a series of SQL 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. Depending on the type of value returned, each user-defined function can be divided into the following three categories:

1. Returns a function that updates the data table: if the 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. Returns a function that does not update the data table: If a user-defined function contains more than one SELECT statement, or contains a SELECT statement that is not updatable, the result of the table format returned by the function is not updatable.

3. A function that returns a scalar value: A user-defined function can return a scalar value.

Stored Procedures

1. Powerful, less restrictive
2. Cannot directly reference the return value
3. Returning a Recordset with a SELECT statement

Custom functions

1. Many limitations, many statements can not be used, a lot of functions can not be implemented
2. Return values can be referenced directly
3. Returning a recordset with a table variable

Reprint SQL stored procedure and function difference