The Command Object - The Parameters Collection

(Page 6 of 8 )

When dealing with stored procedures or queries that accept arguments, you have three options:

• Use the Command object's Execute method, passing in the parameters as an array of values into the second argument of this method. Not all providers support this.

• Use the Command object's Execute method, passing the parameters in the Command's Parameters collection.

Use the Command object's Execute method, passing the parameter values as part of the command text. In many cases the first is acceptable (and is in fact easier to code), but the one restriction of this is that you can't return any values from your stored procedure. For example, suppose you have a stored procedure like this:

  CREATE PROCEDURE usp_AuthorsByState 
     @RequiredState   char(2),
     @ID              integer    OUTPUT
  AS
  BEGIN
      SELECT *
      FROM   authors
      WHERE  state = @RequiredState
      SELECT @ID=123
      RETURN 456
  END

This procedure does three things:

1. It returns a recordset using one of the parameters to restrict the rows that are returned.

2. It sets the output parameter to an arbitrary value (123 in this case).

3. It returns another arbitrary value (456 in this case).

Although the output value and return value are falsely constructed, it illustrates two common techniques for returning information from a stored procedure.

If you wished to call this stored procedure without using the Parameters collection, you could do so using code like this:

  objCmd.CommandText = "usp_AuthorsByState"
  objCmd.CommandType = adCmdStoredProc
  Set objRec = objCmd.Execute (, Array("CA", lngID))

This simply calls the procedure, passing in two arguments: CA to filter the recordset and lngID (a long integer) to be used as the output parameter. This works fine, because ADO builds a Parameters collection from the values you supply. However, note that the output parameter (lngID) is never populated with the output value, and there's also no way to access the return value. So although this method is extremely easy to use, it doesnt give you the functionality that the Parameters collection does.

There's a caveat to this approach: If you reference the Parameters collection before calling the Execute method, the Parameters collection is then Refreshed automatically for you. For example:

  objCmd.CommandText = "usp_AuthorsByState"
  objCmd.CommandType = adCmdStoredProc
  Print "Parameter Count = " & objCmd.Parameters.Count

During the Print statement, ADO automatically calls a Refresh and populates the Parameters collection. The key point is that there are three parameters, not two: the return value, the input parameter (the required state), and the output parameter (the ID value). This means that the next line might fail, because it doesn't use enough parameters:

  Set objRec = objCmd.Execute (, Array("CA", lngID))

However, this line works:

  Set objRec = objCmd.Execute (, Array(lngRV, "CA", lngID))

This shows that if you manually populate the Parameters collection or use the Array method, you don't need to worry about the return parameter. However, if you Refresh the Parameters collection (or if it is refreshed implicitly for you), you'll find that the return value becomes the first parameter in the collection.

Note that not all providers or versions of SQL Server may support this automatic refresh (or indeed the Refresh method itself). If using SQL Server 6.5, ensure that you have installed the latest Catalog Procedures. See the SQL Books online for more details on this.

Differences from ADO 2.0

The automatic refreshing of parameters in ADO 2.1 onward is different from what occurred in ADO 2.0. Using an explicit Refresh works for both the OLE DB Provider for ODBC and the OLE DB Provider for SQL in both versions of ADO. However, using the implicit refresh gives different results.

Ive used the following code to test the automatic refresh facility in ADO 2.0 and ADO 2.1 onward:

  objCmd.CommandText = "usp_AuthorsByState"
  objCmd.CommandType = adCmdStoredProc
  Print "Parameter Count = " & objCmd.Parameters.Count

If you are using SQL Server 6.5 (and you haven't updated your Catalogs), only the OLE DB Provider for ODBC will return the correct parameter information to the Parameters collection. If you have updated your catalogs, or if you're using SQL Server 7, then there are differences between the ways that the two versions of ADO handle the parameters. The following list shows which combinations of ADO, providers, and data stores support automatic refreshing of parameters:

---

ADO Version	Provider	SQL Server	Supported
2.0	OLE DB for ODBC	6.5	Yes
		7.0	Yes
	OLE DB for SQL	6.5	Yes
		7.0	No
2.1 onward	OLE DB for ODBC	6.5	Yes
		7.0	No
		2000	No
	OLE DB for SQL	6.5	No
		7.0	No
		2000	No

Don't think that this removal of functionality is a detrimental step. In this case it is a deliberate (and documented) move to improve performance and reduce network traffic.

Parameter Types in ASP

When using the array method in ASP scripting languages, you must make sure that any variables used for output parameters have the correct data type. When you declare variables in scripting languages, they are variants, and this may not match the correct parameter type. The output parameter must have the correct type, even though you can't actually use it. For example, in VBScript:

  Dim lngID
  Set objRec = objCmd.Execute (, Array(lngRV, "CA", lngID))

This produces an error, which indicates that a parameter object has not been supplied. That's because lngID is a variant and doesn't have a default type or value. However, this section of code works:

  Dim lngID
  lngID =
  Set objRec = objCmd.Execute (, Array(lngRV, "CA", lngID))

This forces the variant to hold the correct data type; it gives lngID a sub-type of Integer, which is compatible with the integer type declared in the stored procedure (SQL Server integers map to Longs in Visual Basic and in scripting languages).

The Parameters collection contains a Parameter object for each parameter in a stored procedure, including the return value if the procedure has one. It has only two properties and three methods.

Next: Methods of the Parameters Collection >>
 

More Database Articles
More By Apress Publishing

Comments On: The Command Object

>>> Be the FIRST to comment on this article!

Buy this book...

This article is excerpted from ADO Programmer's Reference by David Sussman (Apress, 2004; ISBN: 1590593421). Check it out at your favorite bookstore. Buy this book now.