Advantages and Disadvantages of Using Stored Procedures - SQL

A Stored Procedure is a type of code in SQL that can be stored for later use and can be used many times. So, whenever you need to execute the query, instead of calling it you can just call the stored procedure. You can also pass parameters to a stored procedure, so that the stored procedure can act based on the parameter values that is passed.

Syntax :

CREATE PROCEDURE procedure_name
AS
sql_statement
GO; 

To execute a stored procedure -

EXEC procedure_name;

Example :

CREATE PROCEDURE SelectAllGeeks
AS
SELECT * 
FROM Geeks
GO; 

Advantages :

The main advantages of stored procedure are given below:

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 - Stored Procedure helps to encapsulate the SQL logic and business logic due to which it provide reusability and modularity.
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 given below:

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).