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.
Prev. page
[1]
2
next page 
