Access parameter-based stored procedures with fewer lines of code and fewer problems
When I spoke at a large SQL Server conference recently, I asked whether anyone used stored procedures. A waving sea of hands answered me—it seemed that nearly everyone used stored procedures. To an experienced SQL Server developer, the answer to my question might seem obvious—you might as well ask my 9-month-old granddaughter whether she likes cookies. The developers I hear from depend on stored procedures for performance, scalability, and database management. But to a SQL Server newcomer, especially someone migrating from Microsoft Access, stored procedures can be somewhat daunting. However, ADO makes accessing stored procedures easy. ADO is a universally accepted way to access data from Visual Basic (VB), C++, or VBScript in Active Server Pages (ASP). Microsoft designed ADO to be able to execute stored procedures more efficiently than its predecessors—Data Access Object (DAO) and RDO. You can efficiently execute parameter-driven stored procedures from ADO by using a new technique Microsoft introduced in ADO 2.6. This technique lets developers access parameter-based stored procedures with fewer lines of code and with fewer problems from passing in default or unneeded parameter arguments. To a middle-tier component or ASP, having fewer lines of code yields better performance. And fewer lines of code also means fewer coding mistakes on the developer's part.
How ADO Can Help
ADO versions earlier than 2.6 can't deal with "sparse" parameter lists. Since the early days of stored procedures, you could use Interactive SQL (ISQL) or DB-Library to invoke a stored procedure by passing just the parameters needed—leaving the remaining parameters to take on their default settings (as determined by the stored procedure declaration). Unfortunately, data-access developers have been unable to use this technique since DB-Library became obsolete. Such Microsoft-provided data-access interfaces as DAO, RDO, and pre-2.6 versions of ADO support only positional parameters. That is, when you want to execute a stored procedure, you have to describe and provide all the parameters when calling the procedure—unless you bypass the managed interface and hard-code the stored procedure call in a string. Even in the most recent versions of ADO, when you want ADO to manage a stored procedure's parameters, you usually have to construct a Command object to describe the stored procedure by name and build a Parameters collection. You must define each parameter in the precise order and fashion in which it's described in the stored procedure declaration. This process can be tedious and can lead to incorrect code. And if the DBA adds parameters to your stored procedure after you write the front end or component, you have to recode and redeploy the application or component calling the stored procedure or write some pretty fancy code that can accommodate the changes.
More important, the positional parameter approach means that if you want to reference only the fourth of 44 parameters, you're out of luck—you still have to provide a value for each parameter. Defining a stored procedure with default parameter values is possible, just as defining an ADO Command object with default parameter values is possible. However, if the Parameters collection doesn't include the same number of parameters as defined in the stored procedure, ADO won't be able to execute the stored procedure.
SQL Server has supported named parameters in stored procedures for as long as I can remember. Using named parameters with stored procedures means you can execute a stored procedure and pass only the specific parameters that make sense at the time. The other parameters take on the default value as currently defined in the stored procedure. ADO 2.6 exposes named parameter management for the first time.
The trick to efficiently executing a stored procedure is to make only one round-trip to the server. A couple of common coding mistakes encourage ADO to construct the Command object's Parameters collection for you, which requires ADO to make an extra round-trip to the server to fetch a server-provided description of the procedure to execute. One mistake is using the Command object's Refresh method after design time. As you develop your application, you might find it useful to initially determine what the Parameter objects should look like, especially when you aren't sure how to describe one or more Parameter objects to match the corresponding stored procedure parameter. For example, you might want to know the parameter's varchar size, data type, or numeric precision. The Refresh method can automatically construct the individual Parameter objects and construct the Parameters collection for you by making a round-trip to the server. You can then use VB's break mode and the VB Locals Window to examine the Command Parameters collection item by item. However, be sure to remove the Refresh method before you put the procedure into production and construct the Parameters collection in your code, parameter by parameter. Because the VB Data View Window makes a round-trip to the server to describe selected stored procedures at design time, you don't incur any runtime performance penalties. From this window, you can build your Parameter objects, then append them into the Parameters collection.
Another common mistake is touching the Parameters collection too early. If you write code that sets the ActiveConnection property to a live connection and you subsequently reference the Parameters collection before beginning to append the individual Parameter objects to the Parameters collection, ADO will use the Refresh method to construct the Parameters collection behind the scenes. To avoid this problem, just remember to set the Command object's ActiveConnection property last so that ADO will let you construct the Parameters collection. Then, when you first reference the Parameters collection (usually when you begin to append new Parameter objects), ADO has no way to run the Refresh method on its own.
Best Techniques
So, what's the best way to call stored procedures with ADO? Because so many options exist, no one way of calling stored procedures is best in all situations. However, the following techniques are easy to code and don't overburden the server with needless overhead.
Avoid using the Command object. The ADO Command object is the only way to construct a Parameters collection. However, keep in mind that you need the Parameters collection only when you must deal with the parameters that SQL Server returns. If your stored procedure doesn't return output parameters and you don't need the Return Value parameter, you don't need a Command object or a Parameters collection at all.
One of ADO's least understood yet most powerful features is its ability to execute stored procedures or named Command objects as methods of the Connection object instead of using the Command object Execute method. For example, if you want to use ADO to execute a stored procedure named Fred, you can simply use the existing Connection object (Cn in this case) as follows:
Cn.Fred
If the stored procedure has input parameters, you can provide them positionally as arguments to the method. For example, if the stored procedure accepts two arguments, Soccer and 5, your code could execute the procedure as follows:
Cn.Fred "Soccer", 5
If the stored procedure returns rows in the first or a subsequent resultset, you simply pass an instantiated Recordset object as the last argument to let you manage the rowset:
Set myRS = New ADODB.Recordset
Cn.Fred "Soccer", 5, myRS
You can use SQL Server Profiler to find out which statements ADO sends to SQL Server. You'll discover that ADO sends only a request to execute the stored procedure—nothing more. ADO doesn't construct any Command or Parameter objects; it simply executes the stored procedure and passes back the first rowset to the Recordset (if you specified one).
Prev. page
[1]
2
next page 
