Programmatically Discovering the Parameters of ...
© 2004 Microsoft Corporation. All rights reserved.
Programmatically Discovering the Parameters of a Stored Procedure
One way to pass parameters to a stored procedure is to build an ADO parameters collection manually. Such code generally looks something like this:
'Assume cmd already Dimmed and Set as ADODB.Command
'Assume prm already Dimmed as ADODB.Parameter
Set prm = cmd.CreateParameters("@Parameter", adInteger, _
    adParamInput)
cmd.Parameters.Append prm
Set prm = cmd.CreateParameter("@Parameter2", adVarChar, _
    AdParamInput, 40)
cmd.Parameters.Append prm
cmd.Execute
      However, if you are working against a SQL Server 7.0 database, you can set up the command object and call the Refresh method of the Parameters collection object and have the database populate the Parameters collection for you. You can then access these directly using their name as an index. That code would look something like this:
'Assume cmd already Dimmed and Set as ADODB.Command
With cmd
    .Parameters.Refresh
    With .Parameters
        .Item("@Parameter").Value = 10
        .Item("@Parameter2").Value = "Some Text"
    End With
    .Execute
End With
      Using the latter approach offers several advantages. First, if someone decides to change the name of a parameter in the database, you will be able to find the exact parameter that was changed because an error will be raised when you attempt to access it, whereas the first approach will not raise the error until you execute the command. The second approach also allows you to loop through the Parameter objects, query their properties, and act on them accordingly. This is the approach we used to construct the XML representation of the parameters that are streamed to the data access adapter component.


Page view tracker