1. Introduction

• The Stored Procedure is a group of Transact-SQL statements that have been compiled and stored in SQL Server under a certain name and processed as a unit (rather than multiple individual SQL statements).

Before exploring pros or cons, how to optimize a stored procedure. You must have knowledge of SQL as well as basic queries in SQL …

2. Advantages

1. Good performance: procedure calls are quick and efficient because stored procedures are compiled once and stored in executable form. Executable code is automatically cached, thus reducing memory requirements. When executing an SQL statement, SQL Server must check the permission to see if the user who sent the statement is allowed to execute the statement, and then check the syntax to create an execute plan and execute. If there are many such commands sent through the network, it can reduce the speed of the server. SQL Server will work more efficiently if the stored procedure is used because the sender only sends a single statement and SQL Server only checks once then creates an execute plan and executes. If the stored procedure is called multiple times, the execute plan can be reused so it will work faster. In addition, the syntax of the SQL statements has been checked by SQL Sever before saving, so it does not need to be checked at execution.
2. Higher productivity: because it is possible to reuse without having to rewrite SQL statements over and over, so productivity is higher.
3. Scalability and maintenance: storage procedures increase scalability by isolating application processing on the server. If you need to make any changes, just change the stored procedure without touching the application code.
4. Good security: suppose we want to restrict the direct data access of a certain user to a few tables, we can write a stored procedure to access the data and only allow that user to use stored The procedure is already written and cannot “touch” those tables directly. In addition, stored procedures can be encrypted to enhance security.
   …

3. Cons

1. Testability: business logic is encapsulated in stored procedures so it is difficult to check (if checked). Writing tests for any business logic in a stored procedure is not possible, because there is no way to clearly separate business logic.
2. Debugging ability: depending on the database management systems, debugging the procedures will not be possible or extremely difficult to understand. For example, SQL Server has the ability to correct errors and other database management systems do not. This is very difficult for programmers. (on a typical business development platform)
3. Fear of change: One of the biggest drawbacks of stored procedures is that it’s extremely difficult to know which parts of the system use them and which are not. Especially if the software is divided into multiple applications, it is often not possible to find all references in one go (or completely if the developer does not read access to all projects) and because It is difficult to confidently set up changes that will certainly affect the overall system. As a result, stored procedures are at great risk of making breaking changes and development teams often avoid making any changes. Sometimes this can lead to paralysis of new technological innovations.
   …

4. Conclusion

• These are some of the problems that individuals learn and encounter. There are obviously many good reasons to use it, but besides, the drawbacks are quite a trade-off.
• Posts can skip a lot about stored procedures. Please comment in comments.