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) = 1980
Cmd(2) = 1981
Set 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 = 1980
Cmd.Parameters(2).Value = 1981
Set 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.
- You must use the correct parameter name when constructing the Parameters collection. That is, if NamedParameters=False, it doesn't matter what you name the individual Parameter objects (i.e., what you set the Name property to). But when NamedParameters=True, you have to look up the correct stored procedure parameters and make sure to use the same name in the CreateParameter method call you use to construct the individual Parameter object. (The stored procedure add-in constructs the Parameter object for you.) This name will always begin with the at (@) symbol. For example, in the stored procedure that Listing 2, page 50, shows, the named arguments are @YearLow and @YearHigh.
- The order in which you define the Parameter objects is no longer important, with one exception: If you specify the @RETURN_STATUS parameter (it must be named @RETURN_STATUS), you must define it as the first parameter. You can specify all other parameters in any order—or not at all—as long as you use default values to define the server-side parameters.
- You have to construct Parameter objects only for stored procedure input parameters that you want to specify. If you want to execute a stored procedure and specify only a few of 40 input parameters, you simply need to construct properly named Parameter objects for these specific server-side parameters. You can specify these Parameter descriptions in your VB program in any order—unless you specify @RETURN_STATUS, which has to be first.
- You must construct individual Parameter objects for all output parameters. However, you can specify these objects in any order.
- ADO saves the returned output parameters' values to the named Parameters collection Parameter object, regardless of the order defined in the Parameters collection or the stored procedure declaration.
- When you use ADO to call a stored procedure, you still have to reference the input parameters somehow or accept the default values. ADO 2.6 hasn't changed how you pass parameters to the Execute method or how you reference the Parameter objects within the Command object's Parameters collection. The real change in ADO 2.6 is that you don't have to declare all the input parameters and construct them as Parameter objects—as long as you can accept the server-side default values specified in the stored procedure.
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 billva@nwlink.com.
End of Article
Prev. page
1
[2]
next page -->
