Accessing SQL Server Named Parameters with ADO 2.6

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.RecordsetCn.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).

Use an add-in to construct the Parameters collection. When you're working with stored procedures that have many parameters, constructing the Parameters collection parameter by parameter is a pain. Fortunately, an easier way exists. First, if you use ADO 2.6, you have to define only the specific parameters you need for a particular invocation. That is, if the Command object you're building is executed when only a few of the parameters need to be set, you can append just those Parameter objects to the Parameters collection. If you must construct a large Parameters collection, you can use a VB stored procedure add-in that builds the Parameters collection for you. A version of this add-in, which Microsoft distributed at DevDays a few years ago, is available (with the source code) on my Web site (http://www.betav.com). This add-in executes the Refresh method and constructs the Parameters collection behind the scenes at design time, when you don't incur a performance penalty. In an instant, you can have a fully populated Parameters collection.

Provide a default value. You can—and should—provide a default value for your stored procedure parameters in two places. The first place is in the server-side stored procedure declaration. Defining default values for a parameter isn't always possible, nor is it a good idea—some parameters should always require a value. When you define default values for your input (or input/output) parameters, the server simply inserts the default value if a parameter is missing from the parameters you pass to the server at execution time.

The second place where you should specify the default value for a parameter is in the last argument of the Command object's CreateParameter method, which sets the Value property of the Parameter object. When you execute the Command object, ADO passes this value to the server unless you specify another parameter at runtime. However, filling in parameters at runtime can be tricky. If you specify a parameter as an argument to the Execute method or when executing the stored procedure as a method on the Connection object, the Parameter object's Value property is unaffected. The cn.Fred "Soccer", 5, myRS example illustrates how you pass parameters to the stored procedure as method arguments of the Connection object, as does the example that Listing 1 shows. Listing 1's code is an example of the code you need to construct the Command object and its Parameters collection and execute the Command. Note that the default parameters are 1947 and 1950 for the first and second input parameters, respectively.

In Listing 1, the Value properties for each Parameter object are unaffected. If you executed the Command again without providing parameters, ADO would use the original (default) values of 1947 and 1950 in the query.

Therefore, if you don't supply specific values for your parameters, ADO fills in the unspecified parameters with the Value property defined in the Parameter object at runtime. However, if you reference the Parameter object's Value property directly or indirectly (by using default COM property settings), ADO sets a new default value and uses this value the next time—unless you override it.

The code snippet

Cmd(1) = 1980Cmd(2) = 1981Set myRS = Cmd.Execute

resets the Value property of the two input parameters to 1980 and 1981, respectively, overwriting the original default Value settings. Remember, constructing a Parameters collection is necessary only when you need to capture output parameters or the Return Value integer that a stored procedure returns. Here's a tip: Although the preceding code executes quickly, it uses default COM property references, which Microsoft won't support in future versions of VB. To be safe, you might prefer to code

Cmd.Parameters(1).Value = 1980Cmd.Parameters(2).Value = 1981Set myRS = Cmd.Execute

Passing Parameters by Name

As I mentioned, ADO 2.6 now features the ability to manage the named parameters that you pass to stored procedures. If you set the Command object's NamedParameters property to True, ADO deals with the client/server interface in a new way, unlike the way previous versions of DAO, RDO, or ADO do. Several things change when you set NamedParameters to True.

One of the most CPU-consuming aspects of COM is how it addresses program variables. COM binding refers to the process of assigning memory locations (addresses) to program variables and objects so that they can be referenced at runtime. Simply put, if the compiler (VB or any COM-based language) can resolve the address at compile time, your program runs faster. This happens because the compiler requires no extra runtime code to figure out what memory location your program is referring to.

As the code examples show, you can reference individual Parameter objects, including those that use late COM binding, in several ways. Late binding indicates that the compiler deferred binding until runtime; pre-binding is a term I coined to refer to a technique in which a specific object's location is saved for later use, further reducing COM binding overhead. (For more information about pre-binding, see "ADO Performance Best Practices," February 2001.) Unless you refer to the Parameter objects by ordinal number or pre-bind to these objects, your runtime performance can suffer, at least slightly.

Here's an example of passing parameters by name. Listing 3 contains the parameters declaration for a somewhat more cumbersome stored procedure—it has more parameters than the example in Listing 1. Listing 4 shows the code to execute this stored procedure. In this case, you construct a new Command object, name it MySP, and set the properties to call a stored procedure named OutputTitleByISBNandType. The code tells ADO it expects to use named parameters by setting the NamedParameters property to True and subsequently constructs the Parameters collection for those parameters for this Command object to reference. All the undefined parameters will take on their default values when the Command is executed. When you execute the Command object, you can specify one or both of the two input parameters because you're passing them by name instead of by position to SQL Server.

When passing parameters to a stored procedure, you often have to pass a NULL value to indicate that the value is unknown. VB can be somewhat restrictive about passing NULLs, so remember to use the Null keyword in VB (not vbNull, which resolves to 1) as the parameter value.

Passing ordered and named parameters to stored procedures can make you and your applications much more productive. These ADO 2.6 innovations can only make working with stored procedures easier. If you have an idea or question that you'd like to read more about, drop me a line at [email protected].