What are the advantages of stored procedures?

A stored procedure is a set SQL statements compiled into a single execution plan which are ready for execution. A SQL stored procedure is similar to procedures in other programming languages. Following are some of the advantages of using stored procedure:

1. Modular Programming

You can write a stored procedure once, then call it from multiple places in your application.

2. Performance

Stored procedures provide faster code execution and reduce network traffic.

3. Faster Execution

Stored procedures are parsed and optimized as soon as they are created and the stored procedure is stored in memory. This means that it will execute a lot faster than sending many lines of SQL code from your application to the SQL Server. Doing that requires SQL Server to compile and optimze your SQL code every time it runs.

4. Reduced Network Traffic

If you send many lines of SQL code over the network to your SQL Server, this will impact on network performance. This is especially true if you have hundreds of lines of SQL code and/or you have lots of activity on your application. Running the code on the SQL Server (as a stored procedure) eliminates the need to send this code over the network. The only network traffic will be the parameters supplied and the results of any query.

5. Security

Users can execute a stored procedure without needing to execute any of the statements directly. Therefore, a stored procedure can provide advanced database functionality for users who wouldn’t normally have access to these tasks, but this functionality is made available in a tightly controlled way.