Using Stored Procedures

[thiruvasagamani]

Stored procedures exist outside of your application in the database exposed by the provider.

At their simplest, stored procedures are precompiled SQL Select statements.

In most modern DBMSs, however, stored procedures have additional capabilities beyond just basic SQL syntax. These additional capabilities allow stored procedures to receive and return parameters. For DBMSs such as Microsoft's SQL Server, stored procedures can operate as functions with their own return values, as well as accept and return parameters..

Stored procedures can even behave as programming routines in their own right, permitting flow-of-control constructs such as looping and branching, and allowing temporary storage of intermediate information in declared, typed variables.

Stored procedures have several advantages over on-the-fly SQL statements:

      Stored procedures are efficient. A stored procedure is compiled when it is created and is stored and invoked as a compiled routine. Therefore, when your application causes the provider to run a stored procedure, the stored procedure will run more efficiently than a dynamically created SQL statement, which must be compiled by the provider before it can run. Also, the stored procedure will be executed server side, and not with the client workstation's resources.
   

      Stored procedures enforce standards. Because a stored procedure exists in the database, it can be maintained by the appropriate administrators who can change it to keep up with changing requirements. Stored procedures are therefore one way to enforce middle-tier business rules throughout a database. For instance, a stored procedure named "Compute_Commission" might use a different algorithm this year from the algorithm that it used last year.
 
Stored procedures make programming simpler. Because a stored procedure moves processing and logic to the middle tier of a client/server system, there is just that much less processing and logic to perform in the user-interface or application tier. After it has been written, tested, and established in a database, a stored procedure can be used as a "black box" component of an application.