There are a number of ways to access data in database management systems. A large number of resources discuss the ways for getting data in and out of databases and the best ways to do that. Out of these numerous resources support the use of stored procedures. So why should we use stored procedures anyways? In this article I will discuss that what should we use stored procedures to ensure the safety of the data.

Stored Procedures: Abstraction

Stored Procedures adds abstraction layer to the design of a software system. As long as the interface on the stored procedure remains the same, then the fundamental table structure can change with no noticeable consequence to the application that is using the database.

Stored Procedures: Security

Stored procedures also helps put up an extra obstacle to the hackers. The access to data is permitted via stored procedures only and hence permission does not need to be explicitly set on any of the tables. Therefore none of the tables need to be exposed directly to outside applications. For an outside application to modify the database, it must go through stored procedures.

Stored Procedures: Handling queries

Suppose a database has to be denormalized to gain extra performance. In this scenario the stored procedures can handle the additional updates and inserts necessary to ensure the integrity of the data. Without this the each of the queries would have make sure that these changes had taken place. The stored procedures does not waive away the database design phase but it can be helpful if the perfect normalized model has to be denormalized performance improvements.

Stored Procedures: Input Validation

Stored procedures can be written to validate any input that is sent to them to ensure the integrity of the data further than the simple constraints available on the tables. This can help prevent SQL Injections. Parameters can be checked for valid data types and ranges. Information can be cross checked with data in other tables.

Stored Procedures: Performance

Stored procedures also give performance boost to the system. Stored Procedures are precompiled statements so when they are executed there is no additional lag as the SQL is parsed, compiled, execution plans drawn up and then run, they just run because all that extra work is done at the time the create_procedure or alter_procedure commands are run rather than when procedures themselves are run.

Stored Procedures: Work Load

Stored procedures improve performance by moving all work load to the server in one go. A stored procedure can perform a series of queries and return many tables in, what is to the outside world, one operation. This saves the calling process from making many requests and the additional time of several network roundtrips. Furthermore, if the contents of one set of data being returned is dependent on the results of a previous set of data that is being retrieved through the same stored procedure, that the data only has to flow from the database server to the application. If stored procedures were not being used it would mean that the data from the first database call has to get sent back to the database for the second call in order for it to continue retrieving the information needed by the application.

For example Gurand traders send a quarterly statement to its CLIENTS for which some information needs to be extracted from the database. The tables CUSTOMER, ORDER and ORDER_DETAILS are used. This information could be retrieved in several steps by calling the database for each set of information as it is needed to generate the statements.

SELECT * FROM CLIENTS WHERE CustomerID = @CustomerID
SELECT * FROM ORDERS WHERE CustomerID = @CustomerID AND OrderDate>=@StartDate AND OrderDate<=@EndDate
SELECT * FROM ORDER_DETAILS WHERE OrderID = @OrderID

Assuming that the customer in question is "Richard Canyon Beck" and the period for the statement is Q5 2007 then that is 5 roundtrips to the database and 5 times the database has to parse some SQL. This over headed task can be done by a single stored procedure that takes only one trip to the database and is precompiled.

CREATE PROCEDURE GetQuarterlyStatement
@CustomerID nvarchar(5),
@StartDate datetime,
@EndDate datetime
AS
SELECT * FROM CLIENTS WHERE CustomerID=@CustomerID
SELECT * FROM ORDERS WHERE CustomerID=@CustomerID AND OrderDate>=@StartDate AND OrderDate<=@EndDate ORDER BY OrderDate DESC
SELECT ORDER_DETAILS.* FROM ORDER_DETAILS
INNER JOIN ORDERS ON ORDER_DETAILS.OrderID = ORDERS.OrderID
WHERE CustomerID=@CustomerID AND OrderDate>=@StartDate AND OrderDate<=@EndDate ORDER BY OrderDate DESC GO

The stored procedure is now doing in one trip what previously took 5 trips. Of course, this example is somewhat unnatural for briefness, in a real application there would be joins to the product tables, and the columns would be listed rather than using SELECT * and so on.

By managing shared logic in one place at the server, you can simplify security, administration, and maintenance. Client applications can call stored procedures that run SQL queries with little or no additional processing.

Stored Procedures: Network usage

Using a stored procedure can result in reduced network usage and better overall performance. A client application passes control to a stored procedure on the database server. The stored procedure then performs intermediate processing on the database server without transmitting unnecessary data across the network. Only the records that are actually required by the client application are transmitted. A stored procedure can do the processing on the server, and transmit only the required data to the client, which reduces network usage.

Stored Procedures: Efficiency

In a database application environment, many tasks are repeated. A single precompiled stored procedure can provide a highly efficient way to address these recurrent situations.

Stored Procedures: Memory Utilization

Applications that use stored procedures have access to increased memory and disk space on the server computer and to the software that is installed only on the database server.

 More Database Articles
   Database Security: Step by step guideline
   Reusing Databases - A good approach
   Great Tips for Oracle Wrap Utility!!
   Quick Oracle Database Recovery with Minimal Downtime!!
   Speed Up Oracle Data Loading!!