Use ADO to Maximize Stored Procedures

ADO provides full support for stored procedures

If you don't use stored procedures for data access in your ADO/SQL Server applications, you're missing out on a SQL Server feature that can improve performance, maintainability, and security. ADO provides full support for stored procedures, which makes the data-access process relatively simple. In this article, I take you on a tour of techniques to invoke the most common types of stored procedures. I use examples from a demo application written in Visual Basic 6.0 (VB) with a SQL Server 7.0 back end accessing the Northwind database. You can download this code at http://www.sqlmag.com at the link to this article. Before I discuss the code, here's a quick review of the benefits of using stored procedures for data access.

Benefits of Stored Procedures

The main benefits of using stored procedures in SQL Server are increased performance, maintainability, and security. Here's how you can leverage these benefits in your system.

Performance. One reason why stored procedures improve an application's performance is that their code is precompiled. When a stored procedure is saved in the database, SQL Server parses it for syntactical accuracy and saves the procedure's text in the syscomments table. The first time you execute a procedure, the query processor checks to make sure that the objects the procedure references exist. If they exist, SQL Server compiles the procedure into an execution plan and stores the plan in memory in an area called the procedure cache. Unlike previous releases, SQL Server 7.0 lets all users who are executing the same query share the execution plan (at least the portion that doesn't include user context). As a result, multiple users can call the same stored procedure without incurring compilation and optimization overhead. Figure 1 shows the execution plan from the procedure cache. This method averts the expensive operation of using the query processor for each invocation of the procedure and lets SQL Server determine the best access path for the query at compile time. In fact, in some situations, SQL Server 7.0 extends this architecture to regular SQL statements. SQL Server 7.0's architecture differs from 6.5. In SQL Server 6.5, a private copy of the execution plan is created in the procedure cache for each concurrent user.

Another performance gain from stored procedures is that you can perform multistep processes requiring conditional logic, entirely within a stored procedure. You simply call the procedure and pass it all the parameters required for the process. The procedure can then use Transact SQL (T-SQL) conditional statements to make decisions rather than have the client application submit a query to the server, retrieve the result set, make the decision, and finally submit another query to perform the action. These additional round trips to the server consume network bandwidth, not to mention processing time.

Maintainability. By creating procedures to query, insert, update, and delete data, you can insulate the underlying database schema from the applications that use it. Typically, stored procedures act as an interface that developers use to manipulate the data without being privy to the database's internal structure. So, you can change the database's structure without affecting the application programs, as long as the requisite stored procedures contain the same parameters and return the same number and types of columns that they did before your changes. You don't need to recompile and redistribute client programs when a small part of the database structure changes. To achieve this separation from application programs, you need to perform data access through stored procedures.

Using stored procedures for all data access works well for two-tiered applications, in which the user interface calls the procedures directly, and three-tiered applications, in which COM components, which might be running in Microsoft Transaction Server (MTS), call the stored procedures. Although you can use stored procedures for business logic, you can often better model three-tiered application business rules inside the COM components because they're less database-dependent and more flexible.

Security. The final benefit of using stored procedures, one that closely relates to maintainability, is security. When you create stored procedures to manipulate a database, you can grant EXECUTE permissions to the stored procedures without granting explicit SELECT, INSERT, UPDATE, and DELETE permissions on the tables that the stored procedures reference. For this approach to work, the objects (tables and views) that the stored procedures reference need to have the same owner as the procedures and SQL Server needs to validate permissions only on the stored procedures and not on the underlying objects. Setting the permissions this way provides more granular security on the underlying tables because the procedures can allow modifications on a row or column basis without using views or column-level permissions.

I've laid the groundwork for using stored procedures. Now let's roll up our sleeves and learn the mechanics of using ADO and VB to call different types of stored procedures, including those that accept arguments, modify data, and return values.

Invoking Stored Procedures with Parameters

The key to executing stored procedures with ADO is to use the Command object. This object can encapsulate all the properties and behaviors of a SQL statement into an object, then freely associate with Connection objects at runtime.

To encapsulate a command, the minimum properties that you must set are the CommandText (to specify a SQL statement, procedure, or object name), CommandType (to tell ADO how to interpret the CommandText property), and ActiveConnection (to provide the context to execute the command). You can run the command with the Execute method to return a Recordset object. The CommandType property is particularly important because it lets ADO correctly execute the contents of the CommandText property with an enumerated type that can specify a SQL statement, a stored procedure, or a table or view name. To execute the stored procedure usp_GetCustomers, you can use the code in Listing 1, assuming cm references a valid Connection object.

Most stored procedures, except those that are designed to always return the same result set (such as the one shown above), accept arguments that let you customize the result set. To accommodate result set customization, ADO exposes a collection of Parameter objects for each Command object. The two primary methods for creating and populating the parameters are the Refresh method and the CreateParameter method.

The Refresh Method. After you create a Command object and set its CommandText, CommandType, and ActiveConnection properties, you can invoke the Refresh method of the Command object's Parameters collection. The Refresh method instructs the data provider, either the Microsoft OLE DB provider for ODBC (MSDASQL) or the Microsoft OLE DB provider for SQL Server (SQLOLEDB), to find the appropriate parameters for the stored procedure. In most cases, the Refresh method initiates a round trip to the server to return the parameters. After the Refresh Method populates the Parameters collection, you can set individual parameters by accessing the Value property of the parameter.

In Listing 2, the code uses the Refresh method in the call to the stored procedure usp_GetCustOrders, which returns all the orders including the total amount for a specific customer in the Northwind database. The variable pCustId contains the customer ID, which is passed in as an argument to the VB procedure that contains the code. Keep in mind that when you're retrieving result sets from stored procedures, you need to be aware of the cursor properties you're using. All the examples in this article use the default firehose cursor (or cursorless result set), which SQL Server provides as a forward-only, read-only result set that doesn't use ADO's client-side cursor code. (See Morris Lewis, "How ADO Uses Cursors," September 1999, for more information on ADO and cursors.) The Refresh method is efficient for applications that immediately use the result set after the Recordset is created, but it offers the least amount of functionality.

Although you probably don't want to use the Refresh method in production code because it incurs an additional round trip to the database server, you might want to use the Refresh method during development to discover what ADO uses as the proper data types and directions for your parameters. In addition, you can see the parameter's properties by inspecting the Command object in the VB Watch Window. This method can be beneficial when you aren't sure what parameters the stored procedure accepts or how the T-SQL data type that the stored procedure uses matches the data types available in your programming language. Although the method isn't foolproof, it gives you a place to start.

The CreateParameter Method. The second, more efficient, parameter creation technique is to use the CreateParameter method of the Command object to explicitly create, define, and append the parameters to the Parameters collection. When you put the CreateParameter method here, you ensure that the data provider doesn't need to query SQL Server to retrieve the parameter information, which means that the CreateParameter Method involves fewer steps and is therefore more efficient than the Refresh Method. Listing 3 is functionally the same as Listing 2 except that the code in Listing 3 uses the CreateParameter method rather than the Refresh method to populate the lone parameter that the stored procedure exposes. The contents of stored procedure usp_GetCustOrders is in Listing 4.

Note that when you use the CreateParameter method, you need to provide to ADO the data type, direction, size, and value for the parameter as parameters to the method. In this case, @custid is an input parameter, which is passed by value into the stored procedure. (I named the parameters with an @ symbol so that the name of the input parameter is consistent with the name of the parameter inside the stored procedure.) The trickiest aspect of using the CreateParameter method is ensuring that the data type and size are correct. Mapping the 40 ADO data types to data types SQL Server supports can take some research. Incorrectly specifying data types will cause the execution to fail with unpredictable results, which is a reason to use the Refresh method during development. You also need to use the Append method to explicitly add parameters to the Command object's Parameters collection in the same order that the stored procedure specifies them in.

After you create and populate the Parameters collection with either the Refresh or the CreateParameter method, you can use the collection syntax to specify a value for the parameter before you execute it, as the following example shows.

lcmCust.Parameters("@custid") = pCustId

You can omit the final argument of CreateParameter, which you can optionally use to specify the value. Another option is to pass all the values for the parameters in a Variant array when you use the Execute method of the Command object. For example, assume that a Command object accepts parameters for the customer ID, employee ID, and order date. You can use the Array function to pass all three parameters to the Execute method:

Set lrsCust = lcmCust.Execute(, Array(pCustId, pEmpId,pOrderDate))

Note that in VB, the intrinsic Array function returns a Variant array when you pass a series of comma-delimited values to it to insert into the array.

Invoking Data-Modification Stored Procedures

Another good use of stored procedures is to perform data modifications. When you let stored procedures be the data gatekeepers, you enhance security and maintainability. Again, the primary benefit of stored procedures is that the client code, which runs on a user's workstation or a distributed server, doesn't have to generate SQL or understand the database schema.

ADO Command objects do a good job of supporting the client code running on a user's workstation or a distributed server because you can use Command objects to execute stored procedures, with parameters to perform updates, inserts, and deletes. Rather than creating updateable cursors in ADO, you're better off executing stored procedures with parameters to perform the modification, especially when you're using SQL Server, because you can precisely control the timing and nature of the updates. Listing 5, page 42, contains the code to implement an AddOrderDetail procedure, which you can use to add an order detail record to the database when you call the stored procedure usp_AddOrderDetail. Note that in Listing 5, the Execute method of the Command object passes as a reference to the local variable lngAffected with the constant adExecuteNoRecords. The variable lngAffected passes by reference to the Execute method and ADO subsequently populates it with the number of rows the command affected. In Listing 5, the lngAffected variable specifies the number of rows ADO inserts into the Order Details table. The constant adExecuteNoRecords saves memory by instructing ADO not to create a Recordset object when the command executes.

Command objects that contain multiple parameters, such as the command in Listing 5, are good candidates for declaring in module-level variables. Caching Command objects eliminates the overhead in reconstructing the Parameters collection with each invocation, at the cost of using extra memory.

In Listing 6, the T-SQL stored procedure usp_AddOrderDetail performs the insert into the Order Details table and checks the data to ensure that the quantity of items ordered is greater than zero. The procedure uses the RAISERROR statement to generate an error message, which is sent back to the client. The RAISERROR statement accepts arguments including the message, severity, and invocation state of the error. For frequently used messages, the statement can also accept a system- or user-defined message ID for retrieving the error message from the systemessages table. In VB, RAISERROR triggers the active error handler and populates both the intrinsic Err object and the Errors collection of the ADO Connection object.

Invoking Stored Procedures That Return

Values Output parameters and returned values are information other than a result set, which the stored procedure returns. The stored procedure handles both output parameters and returned values through the Command object's Parameters collection.

Returning Output Parameters. To help understand output parameters, see the T-SQL stored procedure in Listing 7. The stored procedure usp_Get-RevByWeek calculates the total amount of revenue and the number of orders placed for a rolling seven-day period; it calculates from the start date, which is passed in as an input parameter. You perform the revenue calculation when you apply the SUM aggregate function to the Order Details table, which contains each product in each order, and multiply the unit price by the order's quantity. Similarly, you calculate the total number of orders by counting the distinct order ID numbers in the Orders table with the COUNT aggregate function. This stored procedure doesn't return a result set; it returns two output parameters: revenue amount and number of orders considered.

As I noted previously, the Parameters collection handles output parameters, which are analogous to arguments you pass by reference in other programming languages. Listing 8 shows the code to call the usp_GetRevByWeek stored procedure.

You also execute the usp_GetRevByWeek stored procedure in Listing 8 with the adExecuteNoRecords constant because only parameters, and not a result set, return. The usp_GetRevByWeek lets ADO bypass creating a Recordset object for the Command. The main difference between using output parameters and using input parameters is that you use the adParamOutput constant in the CreateParameter method to create the parameters. After the usp_GetRevByWeek is executed, you can use the Parameters collection to read the parameters and reference them by name or ordinal number. In this case, the VB procedure passes the output parameters by reference back to the calling code. If a stored procedure returns a single-row, single-column result set or a single row with only two columns, it's more efficient to return those results in output parameters than as a Recordset object. Returning results as output parameters is efficient because ADO doesn't need to build and maintain a Recordset object on the client.

Return Values. The other type of value that a stored procedure can return is the return value. In SQL Server, for example, all stored procedures return a T-SQL integer identifying the execution status of the procedure. Also, you can use the return value to return data that the client application requires.

A typical use of return values is in self-incrementing identity columns, which you define with a seed value (the value of the first row loaded into the table) and increment values. Keep in mind that each table can contain only one identity column, which SQL Server automatically populates each time it inserts a row. You can use these columns to assign a system-generated unique number to a row in the table. Generally, you use this column as the primary key of the table when a natural key isn't present or when the natural key might require future updates. Listing 9, which you can download at http://www.sqlmag.com at the link to this article, shows how to use an identity column in the usp_AddOrder stored procedure. Here, the primary key of the Orders table is OrderId, an identity column that SQL Server automatically populates when the INSERT statement is executed. After the stored procedure performs an INSERT, the RETURN statement passes back the global variable @@identity. SQL Server populates this global variable, which represents the last identity value it inserts during this connection. If you use the technique in Listing 9, the stored procedure always returns the primary key of the new row representing the new order, which the client program needs to manipulate the order by its primary key value.

To handle the return value, you create a parameter with the constant adParamReturnValue in the first position (ordinal 0) of the Parameters collection. When you use the Refresh method of the Parameters collection, the method adds the return value automatically to the collection as the first element. Listing 10, which you can download at http://www.sqlmag.com at the link to this article, shows the AddOrder procedure and illustrates the technique of reading the return value. This procedure simplifies the process of adding an order by requiring that only two values are inserted into the order-customer ID and ship name.

As you can see, you need to follow only a few rules to use stored procedures effectively with ADO. I hope this brief tutorial will encourage you to use stored procedures to get the most out of your SQL Server and ADO applications.