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.
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.
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.
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.
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.
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.
This policy contains information about your privacy. By posting, you are declaring that you understand this policy:
- Your name, rating, website address, town, country, state and comment will be publicly displayed if entered.
- Aside from the data entered into these form fields, other stored data about your comment will include:
- Your IP address (not displayed)
- The time/date of your submission (displayed)
- Your email address will not be shared. It is collected for only two reasons:
- Administrative purposes, should a need to contact you arise.
- To inform you of new comments, should you subscribe to receive notifications.
- A cookie may be set on your computer. This is used to remember your inputs. It will expire by itself.
This policy is subject to change at any time and without notice.
These terms and conditions contain rules about posting comments. By submitting a comment, you are declaring that you agree with these rules:
- Although the administrator will attempt to moderate comments, it is impossible for every comment to have been moderated at any given time.
- You acknowledge that all comments express the views and opinions of the original author and not those of the administrator.
- You agree not to post any material which is knowingly false, obscene, hateful, threatening, harassing or invasive of a person's privacy.
- The administrator has the right to edit, move or remove any comment for any reason and without notice.
Failure to comply with these rules may result in being banned from submitting further comments.
These terms and conditions are subject to change at any time and without notice.
- Data Science
- React Native
- Cloud Computing
- Java Beans
- Mac OS X
- Office 365
- Tech Reviews
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?)