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.






Add Comment

* Required information
1000

Comments

No comments yet. Be the first!

Most Viewed Articles (in Asp.net )

Severity Code Description Project File Line Suppression State Error CS1061 'string[]' does not contain a definition for 'Any' and no accessible extension method 'Any' accepting a first argument of type 'string[]' could be found (are you missing a using directive or an assembly reference?)

Severity Code Description Project File Line Suppression State Error CS0308 The non-generic type 'List' cannot be used with type arguments.

Microsoft.Identity vs Microsoft.IdentityModel.Clients.ActiveDirectory

Severity Code Description Project File Line Suppression State Warning Found conflicts between different versions of the same dependent assembly.

Event Driven Programming in ASP.net

Advantages of Stored Procedures

What is Code-Behind in ASP.net

Severity Code Description Project File Line Suppression State Error CS0246 The type or namespace name 'JToken' could not be found.

Call an Action in a controller when user clicks a button in View

Development Mode in IIS for Asp.net projects

Button that is only clickable when the checkbox is checked

Pass the same model to multiple views within the same controller

Severity Code Description Project File Line Suppression State Error CS0103 The name 'Encoding' does not exist in the current context

Severity Code Description Project File Line Suppression State Error CS0103 The name 'JsonConvert' does not exist in the current context.

Getting values from appsettings.json ASP.NET

Latest Articles (in Asp.net)

Things to note when changing a function to async in your controller

AmbiguousMatchException: The request matched multiple endpoints.

Call an Action in a controller when user clicks a button in View

Button that is only clickable when the checkbox is checked

Pass the same model to multiple views within the same controller

Severity Code Description Project File Line Suppression State Error CS0103 The name 'Encoding' does not exist in the current context

Severity Code Description Project File Line Suppression State Error CS0103 The name 'JsonConvert' does not exist in the current context.

Passing a model globally to all Views in your Asp.net webapp

Severity Code Description Project File Line Suppression State Error CS0246 The type or namespace name 'JToken' could not be found.

Severity Code Description Project File Line Suppression State Error CS0308 The non-generic type 'List' cannot be used with type arguments.

One client credential type required either: ClientSecret, Certificate, ClientAssertion or AppTokenProvider must be defined when creating a Confidential Client. Only specify one

Severity Code Description Project File Line Suppression State Warning Found conflicts between different versions of the same dependent assembly.

Severity Code Description Project File Line Suppression State Error CS1061 'string[]' does not contain a definition for 'Any' and no accessible extension method 'Any' accepting a first argument of type 'string[]' could be found (are you missing a using directive or an assembly reference?)

Pagination in ASP.net core application

Microsoft.Identity vs Microsoft.IdentityModel.Clients.ActiveDirectory

Related Tutorials

Things to note when changing a function to async in your controller

AmbiguousMatchException: The request matched multiple endpoints.

Call an Action in a controller when user clicks a button in View

Button that is only clickable when the checkbox is checked

Pass the same model to multiple views within the same controller

Severity Code Description Project File Line Suppression State Error CS0103 The name 'Encoding' does not exist in the current context

Severity Code Description Project File Line Suppression State Error CS0103 The name 'JsonConvert' does not exist in the current context.

Passing a model globally to all Views in your Asp.net webapp

Severity Code Description Project File Line Suppression State Error CS0246 The type or namespace name 'JToken' could not be found.

Severity Code Description Project File Line Suppression State Error CS0308 The non-generic type 'List' cannot be used with type arguments.

One client credential type required either: ClientSecret, Certificate, ClientAssertion or AppTokenProvider must be defined when creating a Confidential Client. Only specify one

Severity Code Description Project File Line Suppression State Warning Found conflicts between different versions of the same dependent assembly.

Severity Code Description Project File Line Suppression State Error CS1061 'string[]' does not contain a definition for 'Any' and no accessible extension method 'Any' accepting a first argument of type 'string[]' could be found (are you missing a using directive or an assembly reference?)

Pagination in ASP.net core application

Microsoft.Identity vs Microsoft.IdentityModel.Clients.ActiveDirectory