Programming Tutorials

Advantages of Stored Procedures

By: John Kauffman in Asp.net Tutorials on 2008-12-01  

This article discusses what gives stored procedures their important role in serious database-driven applications. These are the many advantages that stored procedures provide.

Transactions

Although a stored procedure contains SQL commands, once compiled it will interact with SQL Server very differently from the way individual SQL statements (such as those passed from an ADO.NET command object) do. One of the key changes is that the SQL commands in a stored procedure are within transaction scope, which means that either all of the SQL statements in a stored procedure will execute, or none will. This is known as atomicity.

Speed

Unlike standard SQL statements, stored procedures are compiled and optimized by the database server. This optimization involves using information about the structure of a particular database that's required at execution time by the stored procedure. This process of storing execution information (the execution plan) is a tremendous time saver, especially if the stored procedure is called many times.

Speed is also improved by the fact that stored procedures run entirely on the database server - there's no need to pass large chunks of SQL code over a network. For a simple SELECT statement, that might not make a big difference, but in cases where we perform a series of loops and calculations, it can have a significant effect.

Process Control

A stored procedure can take advantage of control flow statements such as IF...ELSE, and FOR and WHILE loops, that are not typically available within a basic SELECT statement. This enables us to handle some quite complex logical operations from within SQL code. Without stored procedures, we'd need to create an object in the data layer to handle looping, producing a large amount of network traffic because of the number of records that would need to be processed.

The use of control flow statements is key to any programming language, and by implementing this functionality in Transact-SQL, SQL Server bridges the gap between our code and the database.

Security

Stored procedures can also act as an additional security layer. For example, we could allow access to a stored procedure that generates an average salary for a company, while never allowing its users to see the salary information directly. If we implement security on our tables to prevent direct access, and then add a layer of stored procedures that users can access, we can enforce relationships and business logic that might otherwise be bypassed. A stored procedure acts a bit like a business object in component development: we don't let people call the data layer directly, instead forcing them to go through the business layer.

Providing a secure database environment in a web application is especially important, since the web server provides a convenient interface for hackers and others that would like to access areas that they have no business being in! The Web exposes our data to the outside world, so there is no such thing as a system that is too secure, or has been checked too many times. Implementing a layer of stored procedures that controls updates, insertions, and deletions can be of significant help.

Reduced Network Traffic

Using stored procedures enables a client application to pass control to a stored procedure on the database server. This allows the stored procedure to perform intermediate processing on the database server, without transmitting unnecessary data across the network.

A properly designed application that processes large amounts of data using stored procedures returns only the data that is needed by the client. This reduces the amount of data transmitted across the network.

Modularization

The modularization of code is a key aspect of using stored procedures. Modularization is not only the process of writing reusable code units; it is also the process of maximizing team talents. If there's a strong database developer on a team, then we can let them write fast and efficient database code - in stored procedures - while the component developers work on the business logic.

Stored procedures enable easier maintenance. They are centralized, so we can reuse existing stored procedures throughout a system, and from external components. They are easier to access, to maintain, and to supervise.