Using Parameters

Prepared statements, stored procedures, and user-defined functions may require the use of parameters. The Parameters collection, which consists of Parameter objects, provides parameter information and data for the Command object. You use the Parameters collection and Parameter objects when the query in the Command object requires parameters.

A Parameter object can serve as an input parameter, an output parameter data, or a return value. The Refresh method of the Parameters collection can force providers to update parameter information; however, this operation can take some time to complete.

The Parameters collection provides parameter information and data for the Command object. You use the Parameters collection and Parameter objects when the query in the Command object requires parameters.

This example shows the creation of an input parameter for a stored procedure using Transact-SQL syntax:

USE NORTHWIND
GO
drop proc myADOParaProc 
GO
CREATE PROC myADOParaProc 
@categoryid int(4) 
AS 
SELECT * FROM products WHERE categoryid = @categoryid
GO

The myADOParaProc stored procedure performs a SELECT query against the products table of the northwind database, taking one @categoryid input parameter in its WHERE clause. The data type for the @category parameter is int, and its size is 4.

Here is the Microsoft® Visual Basic® code:

Dim cn As New ADODB.Connection
Dim cmd As New ADODB.Command
Dim rs As New ADODB.Recordset
Dim prm As ADODB.Parameter
Dim fld As ADODB.Field
Dim provStr As String
          
' Connect using the SQLOLEDB provider.
cn.Provider = "sqloledb"

' Specify connection string on Open method.
provStr = "Server=MyServer;Database=northwind;Trusted_Connection=yes"
cn.Open provStr

' Set up a command object for the stored procedure.
Set cmd.ActiveConnection = cn
cmd.CommandText = "myADOParaProc"
cmd.CommandType = adCmdStoredProc
cmd.CommandTimeout = 15
       
' Set up a new parameter for the stored procedure.
Set prm = Cmd.CreateParameter("CategoryID", adInteger, adParamInput, 4, 7)
Cmd.Parameters.Append prm

' Create a recordset by executing the command.
Set rs = cmd.Execute
Set Flds = rs.Fields

' Print the values for all rows in the result set.
While (Not rs.EOF)
   For Each fld in Flds
      Debug.Print fld.Value
   Next
   Debug.Print ""
    rs.MoveNext
Wend

' Close recordset and connection.
rs.Close
cn.Close

The myADOParaProc stored procedure expects an input parameter with a data type of int and a size of 4. The CreateParameter method is used to create a Parameter object with the following characteristics: the data type is adInteger for an integer, the parameter type is adParamInput for input parameter, and the data length is 4. This Parameter object is also given the name CategoryID. The data value 7 (one of the possible values of CategoryID in the products table) is hard-coded.

After the parameter is specified, the Append method adds the Parameter object to the Parameters collection. The myADOParaProc stored procedure is executed, and a Recordset object is created. The values for the columns of each row in the recordset are printed, and the Connection and Recordset objects are closed.

See Also

Using Return Code and Output Parameters for Stored Procedures