What are the Advantages of Stored Procedures

Question

Hi What are the advantages of Stored Procedures in SQL Server ?

Akkiraju Ivaturi · Answer

SPs also provides security to your database and prevents sql injections. Again if you are having dynamic SQL in your stored procedures the execution plan varies based on the input parameter values. If there are non deterministic functions used in the SPs the performance varies. These being exception conditions in general SPs provides peformance by caching the execution plans.

Jignesh Trivedi · Answer

Hi Arun Most of the point is covered by Satyapriya.... please go through below linkit added advanges.... http://www.java-samples.com/showtutorial.php?tutorialid=1000 http://blog.sqlauthority.com/2007/04/13/sql-server-stored-procedures-advantages-and-best-advantage/ hope this will help you.

Satyapriya Nayak · Answer

Hi Arun The Stored procedure is compiled t-sql program stored as object under the database. It will use the cache execution plan for all the subsequent requests. It can accept parameters. Advantages of the stored procedure It will improve the execution. It will compile once and be stored as an object under the database. The same execution plan will be used for all the subsequent execution. It will reduce the network traffic. It will have only a few bytes of parameters. But the inline query will make more traffic. It will be stored in the SQL Server database and any number of applications which return in the multiple languages/application can utilizes the same stored procedure. The permission can be set to the stored procedure. The stored procedure can be changed the logics without redeploying the application in circumstances. The application developers can work in the front end and the SQL developers they can write the SQL Server. Thanks