Advantages and disadvantages of stored procedure?

Advantages:

1. Stored procedures can be pre-compiled and stored in memory, which can improve performance by reducing the amount of time it takes to execute the query.
2. Stored procedures can provide a level of security by limiting the user’s access to the database. Permissions can be granted to execute the stored procedure, but not to access the underlying tables directly.
3. Stored procedures provide a consistent way to access and manipulate data across applications. This helps to ensure data integrity and consistency.
4. By executing the stored procedure on the database server, it reduces the amount of data that needs to be transmitted across the network.
5. Stored procedures can be reused across multiple applications, which can save time and effort in development.

Disadvantages:

1. Stored procedures can be difficult to maintain, especially if they are complex or if there are a large number of them.
2. Debugging stored procedures can be more difficult than debugging code written in a programming language.
3. Stored procedures are specific to a particular database platform and may not be portable to other platforms.
4. Overuse of stored procedures can result in code bloat and can make the database more difficult to manage.
5. Stored procedures are limited in the types of programming constructs they can use, which can limit the functionality that can be implemented.

Advantages :
The main advantages of stored procedure are:

1. Better Performance –
   The procedure calls are quick and efficient as stored procedures are compiled once and stored in executable form.Hence the response is quick. The executable code is automatically cached, hence lowers the memory requirements.
2. Higher Productivity –
   Since the same piece of code is used again and again so, it results in higher productivity.
3. Ease of Use –
   To create a stored procedure, one can use any Java Integrated Development Environment (IDE). Then, they can be deployed on any tier of network architecture.
4. Scalability –
   Stored procedures increase scalability by isolating application processing on the server.
5. Maintainability –
   Maintaining a procedure on a server is much easier then maintaining copies on various client machines, this is because scripts are in one location.
6. Security –
   Access to the Oracle data can be restricted by allowing users to manipulate the data only through stored procedures that execute with their definer’s privileges.

Disadvantages :
The main disadvantages of stored procedures are:

1. Testing –
   Testing of a logic which is encapsulated inside a stored procedure is very difficult. Any data errors in handling stored procedures are not generated until runtime.
2. Debugging –
   Depending on the database technology, debugging stored procedures will either be very difficult or not possible at all. Some relational databases such as SQL Server have some debugging capabilities.
3. Versioning –
   Version control is not supported by the stored procedure.
4. Cost –
   An extra developer in the form of DBA is required to access the SQL and write a better stored procedure. This will automatically incur added cost.
5. Portability –
   Complex stored procedures will not always port to upgraded versions of the same database. This is specially true in case of moving from one database type(Oracle) to another database type(MS SQL Server).

 

Advantages	Disadvantages
It is faster.	It is difficult to debug.
It is pre-compiled.	Need expert developer, since difficult to write code.
It reduces network traffic.	It is database dependent.
It is reusable.	It is non-portable.

Pros:

1. The company (developers) has lots of experience working with stored procedures.
2. The deployment process is easier when we only have to deploy .sql scripts vs server side code.
3. Stored procedures handle large quantities of data much better; in fact EF has some limitations on how much data can be handled.

Cons:

1. The typical stored procedure with business logic can be a single file with 1000+ lines of code. This is very difficult to work with and debug. It is very difficult to work on a small section of the code that may not be at the beginning of the sp. In C# I can easily encapsulate a small piece of logic into it's own class/method and easily work with it.
2. I can't mock data in a sp. I can only mock the results.
3. The DB's are too large for us to have multiple copies at a given time. We are not permitted to have local copies.
4. Multiple users can access the same table and interfere with a test if the test writes directly to the DB.
5. It may not be feasible or even possible to change data is some tables to set up certain scenarios.

Below are the advantages and disadvantages of stored procedure:
Advantages of store procedure:
Precompiled execution
Reduced client/server traffic
Efficient reuse of code and programming abstraction
Enhanced security controls

Disadvantages of store procedure:
Limited Coding Functionality
Portability
Difficult to Debug.
Not easy to Develop and Maintain
 

SP Advantages:

• Pre-Compiled 
• Better Performance
• Reusability
• Maintainability
• Security

Disadvantages:

• Debugging
• Versioning
• Portability
• Logic suppose to be in DB instead of Front End
• Bit of Expensive