What are the advantages of ms SQL server stored procedures?

1. the stored procedure can be compiled only when it is created. You do not need to re-compile the stored procedure every time you execute it. However, the SQL statement we usually use is compiled every time you execute it, therefore, using stored procedures can speed up database execution.

2. complex business logic and database operations are often encountered. In this case, SP is used to encapsulate database operations. When performing complex operations on the database (for example, performing update, insert, query, and delete operations on multiple tables ), this complex operation can be encapsulated in a stored procedure and used together with the transaction processing provided by the database. It can greatly improve database usage efficiency and reduce program execution time, which is very important in database operations with large data volumes. In terms of code, the separation of SQL statements and program code statements can improve the readability of program code.

3. You can set parameters for stored procedures. You can reuse the same stored procedure based on different input parameters to improve the code optimization rate and readability.

4. High security. You can set the type of stored procedure that only a user can use for a specified stored procedure:

(1) system stored procedure: starts with SP _. It is used to set the system and obtain information. related management work. For example, sp_help is used to obtain information about the specified object.

(2) The extended stored procedure starts with XP _ and is used to call the functions provided by the operating system.
Exec master .. xp_mongoshell 'Ping 10.8.16.1'

(3) User-Defined stored procedure, which is a common format of Stored Procedure

Template: Create procedure procedue_name [@ parameter data_type] [Output]
[With] {recompile | encryption} As SQL _statement

Explanation: Output: this parameter can be returned.

With {recompile | encryption} recompile: indicates that the stored procedure is re-compiled every time it is executed. encryption: the content of the created stored procedure is encrypted.