Benefits of Using Stored Procedures

Usually, stored procedures are used to encapsulate or enforce business rules in your databases. For example, if you have to do some calculations before inserting data in a table, you can embed this logic in a stored procedure and then insert the data using this stored procedure. Similarly, if you don't want users to directly access tables and any other objects, you can create stored procedures to access these objects and have users use them, instead of manipulating objects directly. For example, Microsoft discourages users from making direct modifications to system tables; however, SQL Server comes with system stored procedures to manipulate system tables.

The following are the benefits and advantages of stored procedures:

• They are precompiled statements—An execution plan (or access plan) is created and stored in memory the first time the stored procedure is run, and it is subsequently used each time you execute the stored procedure, thus minimizing the time it takes to run. This is more efficient than executing each statement separately, one by one, because SQL Server would have to generate an access plan for each statement every time it is run.

• They optimize network traffic—You might say that stored procedures aren't related to network traffic at all. However, when you execute a stored procedure that contains many statements, you just have to call the stored procedure once, not each statement separately. In other words, the entire block of code (the whole set of statements) doesn't need to be sent from the client to the server. For example, if you create a stored procedure with 10 statements and execute it, you need to send only one instruction to SQL Server instead of 10 separate instructions. This translates into fewer round trips to SQL server, thus optimizing network traffic.

• They can be used as a security mechanism—In particular, if the owner of an object doesn't want to give direct permissions to users on database objects, he can create stored procedures that manipulate these objects, and then give execute permissions on these stored procedures to users. This way, users will be allowed only to execute these stored procedures, and they won't be able to directly manipulate the objects that stored procedures reference. System stored procedures are an example of this approach. SQL Server provides system stored procedures to prevent users from dealing directly with system tables.

• They allow modular programming—You can encapsulate your business logic inside stored procedures, and then just call them from applications. Therefore, all statements that make up a stored procedure are executed as a whole in the server. Furthermore, you can embed conditional logic in a stored procedure using any of the control of flow statements (IF...ELSE, WHILE) available in Transact-SQL.

• They can be set to execute automatically when SQL Server starts—Any routine task that must be executed whenever the SQL Server service starts can be programmed as a stored procedure and then configured to run automatically using the sp_procoption system stored procedure.

• They can use parameters—This is one of the ways that stored procedures have to receive data from and return it to the calling application. Parameters can be either input, which are similar to variables passed by value, or output, which behave as variables passed by reference.