ADVANTAGES OF STORED PROCEDURES

1. They are PRECOMPILED which is executed as a unit.

2. They provide you faster execution.

3.You don’t have to recompile it again and again.

4.They are less prone to errors as they are written by experienced programmers.

5.Same business  logic can be used across multiple applications.

6.It reduces network congestion.

7.It provides better security.Only DBA owner has permission to modify and make changes to stored procedures. The logic of the stored procedures are also not revealed to others.

8. SP are compiled and their execution plan is cached in SQL SERVER memory and used again to when the same SP is executed again. This means when you execute the SP second time it will take less amount of time as compared to the time taken at the first time.

Other Notes:

1. You have to execute a stored procedure but you cannot select from a stored procedure.

2. Stored Procedures are logical components of SQL Server.

3.Stored Procedures by default does not return any value. They only return whether the Stored procedure has been successfully executed or not.

4.Stored procedures can have two types to parameters. Input and output parameters.

5.Stored Procedures also have recompile option.If we use this option then every time we execute a stored procedure so first it will first compile the stored procedure first then execute it. This is a bad practice to use this option.It is not recommended.

6.If you want to return some thing from the stored procedure then you have to use stored procedures with output parameters.

Pawan Kumar

Pawankkmr@hotmail.com