Advantages of Stored Procedures

By: John Kauffman Emailed: 1676 times Printed: 2157 times    

Latest comments
By: rohit kumar - how this program is work
By: Kirti - Hi..thx for the hadoop in
By: Spijker - I have altered the code a
By: ali mohammed - why we use the java in ne
By: ali mohammed - why we use the java in ne
By: mizhelle - when I exported the data
By: raul - no output as well, i'm ge
By: Rajesh - thanx very much...
By: Suindu De - Suppose we are executing

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.


Asp Home | All Asp Tutorials | Latest Asp Tutorials

Sponsored Links

If this tutorial doesn't answer your question, or you have a specific question, just ask an expert here. Post your question to get a direct answer.



Bookmark and Share

Comments(4)


1. View Comment

it is very very use full to us

View Tutorial          By: santhosh at 2008-12-07 14:46:13
2. View Comment

I used two phrases of the security section for thesis

View Tutorial          By: pabo at 2010-04-05 05:22:24
3. View Comment

it absolutely very usefull for me
Thanks


View Tutorial          By: sudhir Kr Singh at 2012-07-24 05:17:52
4. View Comment

I am certifed java Business Component Developer, mentioning "layer of indirection " was the point of understanding for me. Much appreciated. Siyabonga(Thanks)

View Tutorial          By: Kobs at 2013-02-26 11:58:22

Your name (required):


Your email(required, will not be shown to the public):


Your sites URL (optional):


Your comments:



More Tutorials by John Kauffman
Advantages of Stored Procedures

More Tutorials in Asp
IsPostBack in ASP.net
What is Code-Behind in ASP.net
ASP Versus ASP.NET Events
Lifecycle of a web form in ASP.net
Directives in ASP.net
DataGrid Control in ASP.net
Advantages of Stored Procedures
Event Driven Programming in ASP.net
Advantages of ASP.NET
What is ASP.NET?
Visual Basic .NET Vs Visual C# - (Differences)
IIS and WAP. Configuring IIS to deliver WML (WAP content)
Getting started with ASP
ActiveX component can't create object: 'CDONTS.NewMail' - ASP

More Latest News
Most Viewed Articles (in Asp )
What is Code-Behind in ASP.net
IIS and WAP. Configuring IIS to deliver WML (WAP content)
Visual Basic .NET Vs Visual C# - (Differences)
Advantages of Stored Procedures
IsPostBack in ASP.net
Directives in ASP.net
ActiveX component can't create object: 'CDONTS.NewMail' - ASP
Getting started with ASP
Advantages of ASP.NET
What is ASP.NET?
Event Driven Programming in ASP.net
ASP Versus ASP.NET Events
Lifecycle of a web form in ASP.net
DataGrid Control in ASP.net
Most Emailed Articles (in Asp)
Lifecycle of a web form in ASP.net
IIS and WAP. Configuring IIS to deliver WML (WAP content)
Visual Basic .NET Vs Visual C# - (Differences)
DataGrid Control in ASP.net
What is ASP.NET?
Event Driven Programming in ASP.net
IsPostBack in ASP.net
ASP Versus ASP.NET Events
Directives in ASP.net
Advantages of Stored Procedures
What is Code-Behind in ASP.net
ActiveX component can't create object: 'CDONTS.NewMail' - ASP
Advantages of ASP.NET
Getting started with ASP