T-SQL » Stored Procedures

• Main Index

Creating a Stored Procedure Executing a Stored Procedure

Using Parameters Deleting a Stored Procedure

Modifying a Stored Procedure Renaming a Stored Procedure

To View a Stored Procedure

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:

1. Since the queries are executed as a batch, the network traffic is significantly reduced.
   
2. Execution time is greatly reduced due to the fact that the stored procedures are pre-compiled.
   
3. 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.
   
4. 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.