T-SQL » Stored Procedures
Stored Procedures can be defined as compiled SQL statements stored in the database.
Using stored procedures has many advantages over simply calling a query from the front-end, especially in web applications and large size applications.
Advantages of Stored Procedures:
- Since the queries are executed as a batch, the network traffic is significantly reduced.
- Execution time is greatly reduced due to the fact that the stored procedures are pre-compiled.
- Once a stored procedure is executed, it will reside in the cache of the SQL Server. As a result, any of the subsequent calls will be speeded up.
- Stored procedures provide a different layer of abstraction for the application and has two main advantages.
- First, without changing the application, we can easily modify the stored procedure when business rules change.
- More than that, they provide more efficient and secure data access.