Create Once, Store and Call For Several Times…

What is a Stored Procedure?

A stored procedure is a prepared SQL code that you can save, so the code can be reused over and over again. So if you have an SQL query that you write over and over again, save it as a stored procedure, and then just call it to execute it. You can also pass parameters to a stored procedure, so that the stored procedure can act based on the parameter value(s) that is passed.

What are the advantages of Stored Procedures ?

Performance
Stored procedures are compiled and stored in the database. This allows caching and faster performance on repeated calls.

Reusable
Stored procedures can be used by different applications and users. They can be used instead of writing repeated queries from scratch.

Less traffic
Instead of sending many lengthy SQL queries, the connection can call the stored procedure and get the results in one go.

Secure
Permissions can be granted to a stored procedure separate from tables/columns, so restricted users can use them.

What are the disadvantages of Stored Procedures ?

Memory and CPU usage
Stored procedures add overhead to each connection, which means the database can’t support as many connections.

Difficult to maintain
Like triggers, stored procedures are less transparent and harder to write, especially if the schema of the database changes.

Difficult to debug
Most DBMSs don’t have good support for traceback and error reporting for stored procedures.