There are many advantages of Stored Procedures. I was once asked what do I think is the most important feature of Stored Procedure? I have to pick only ONE. It is tough question.
I answered : Execution Plan Retention and Reuse (SP are compiled and their execution plan is cached and used again to when the same SP is executed again)
Not to mentioned I received the second question following my answer : Why? Because all the other advantage known (they are mentioned below) of SP can be achieved without using SP. Though Execution Plan Retention and Reuse can only be achieved using Stored Procedure only.
- Execution plan retention and reuse
- Query auto-parameterization
- Encapsulation of business rules and policies
- Application modularization
- Sharing of application logic between applications
- Access to database objects that is both secure and uniform
- Consistent, safe data modification
- Network bandwidth conservation
- Support for automatic execution at system start-up
- Enhanced hardware and software capabilities
- Improved security
- Reduced development cost and increased reliability
- Centralized security, administration, and maintenance for common routines
Reference : Pinal Dave (https://blog.sqlauthority.com)
43 Comments. Leave new
If we pass the query to stored procedure and just write a simple exec statement to execute that query, do we still can get benefit of using stored procedure?
Example:
create procedure mySP
@qry varchar(max)
as
exec (@qry)
and @qry is passed through the application code like:
string qry = “select * from myTable”;
Thanks.
its very nice thanks please update it regularly
nice article
why don’t you put sql tutorials on youtube. that will helps every one please
Hi Pinal Dave ..Can you please explain how stored procedure can help in Network bandwidth conservation
If you have 200 lines of query to be executed 50000 times a day, you could have two options:
1. Make your application make database connection with those 200 lines of code.
2. Put those 200 lines in a stored procedure, and just call the stored procedure by name.
From database perspective, though outbound data (from the database to application) size will not have any difference, the inbound data will have significant less amount of “network packets”.
hi this is my question how to short out the lack of records in single table
how to compare deleted data and insert data in sql server
hi so good sir
advantage of store procedure
Thanks Kamal!
From what I know, execution plans are stored for adhoc queries too.