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

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.
It’s security is high . It is expensive.

Attention reader! Don’t stop learning now. Get hold of all the important DSA concepts with the DSA Self Paced Course at a student-friendly price and become industry ready.

My Personal Notes arrow_drop_up

Check out this Author's contributed articles.

If you like GeeksforGeeks and would like to contribute, you can also write an article using contribute.geeksforgeeks.org or mail your article to contribute@geeksforgeeks.org. See your article appearing on the GeeksforGeeks main page and help other Geeks.

Please Improve this article if you find anything incorrect by clicking on the "Improve Article" button below.


Article Tags :
Practice Tags :


Be the First to upvote.


Please write to us at contribute@geeksforgeeks.org to report any issue with the above content.