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.
The following sections discuss how to create stored procedures.