MS Access for the Business Environment: Stored Procedures from the MS Access Client - Page 2

Introduction to Stored Procedures

Like the Pass-Through queries we examined in our last article, Stored Procedures are coded in the dialect of the back end database. Indeed, a Stored Procedure consists, in many cases, of one or more queries just like the one we examined in our last lesson. Stored Procedures are precompiled sets of SQL statements that can perform complex operations on the back-end server that include queries, but can encompass far more.

The Stored Procedure resides on the back-end database server, and exists to perform some action there. The set of SQL statements is given a name, to which we can refer to call the procedure on a recurring basis, to perform its designated action without having to pass the SQL statements repeatedly. This collection and storage of the statements in a modular, reusable object serves as the conceptual foundation of the efficiencies that Stored Procedures offer. A diagram of the execution of the Stored Procedure, on a SQL Server back end from MS Access, is presented in Illustration 1.

Stored Procedures are an attractive option in cases that include one or more of the following considerations:

• Our query will be run repeatedly;
• Our query involves processing that, while potentially complicated, requires little or no user intervention;
• We encounter needs that can only be met with complicated logic, or lengthy coding.

While we cannot exploit all the server-based attributes of Stored Procedures from an MS Access client, we can certainly participate in many of the resulting efficiencies within the attributes that we can employ. The advantages of Stored Procedures that are relevant to us from the MS Access perspective include the following:

• Batching of multiple statements into a single, reusable object;
• Pre-compilation of an execution plan that can be executed repeatedly;
• Reduction in network traffic;
• Control of location of execution;
• Central maintenance considerations.

Some of these advantages accrue to the use of Pass-Through queries in general, while some make the Stored Procedure even more advantageous than an ad hoc Pass-Through query that accomplishes the same actions. In the simplest sense, Stored Procedures provide a readily accessible storage location for our coding: we often lose external files, forget logic from the past or run into multiple version conflicts when maintaining code in external locations. Moreover, the capability to combine multiple statements in a single, reusable object allows us to batch various "steps" that we do together on a recurring basis, so as to permit us to employ the same steps within the execution of a "single motion." As we shall see, calling the Stored Procedure from MS Access is quite simple, and much easier than typing in a query, or set of queries, that we use repetitively.

In addition to the capability of combining multiple statements into a "one-step call," the Stored Procedure object will run more efficiently on the database server, which means earlier completion of its intended actions and, therefore, a more rapid return of the product of those actions (information) to the MS Access client. Because it is parsed and optimized when it is first executed, and a compiled version of the Stored Procedure remains in the memory cache for ready future use, the Stored Procedure does not need to be reparsed and reoptimized with each use. The result is an often significantly faster execution time.

Additionally, Network traffic is reduced when we call a Stored Procedure, rather than pass the code enclosed within the procedure to the server from the client, because, instead of passing a query through Access JET, then ODBC, to the back-end server, we are passing a much smaller EXECUTE command. This command simply calls the Stored Procedure on the server, and all processing takes place on the back-end thereafter, until results are returned to the client. The benefits of using the Pass-Through query (see the list of advantages in Extend Access with Pass-Through Queries) still accrue in that we avoid the operational "taxes" imposed by the JET / ODBC translation processes, but we gain even more efficiency in that we are not even passing a server-sensitive query, but we are passing a mere command to execute a query, etc., that is fully housed on the back-end server.

The advantage of control of processing location is another benefit we gain from using Stored Procedures that we execute from the client. We can center the processing on the server, which is likely to provide substantially greater processing resources. Moreover, the fact that the Stored Procedure resides on the client provides us with the advantage of central maintenance. If we need to modify the code to meet changing business needs, for example, the Stored Procedure provides a single point of maintenance versus the alternative of making changes to every individual client in a client / server application.

Stored Procedures clearly offer advantages over other options when it is possible to use them instead of ordinary queries. Let's examine the steps involved in setting up Stored Procedures, and expose some of the considerations that accompany their use, in the following sections.