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:

  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.