Visual Basic Concepts

Capturing Stored Procedure Output Parameters

Using the same technique used with parameter queries in SELECT statements, you can also capture the output parameters from a procedure.

To capture output parameters

1. Use ODBC escape syntax to establish placeholders for the output and input parameters and return values.
   
   
2. For ODBC data sources that don't automatically determine the direction, your code must set the Direction property of each rdoParameter object to indicate how the parameter is used.
   
   
3. For ODBC data sources that don't automatically (or correctly) determine the data type for individual parameters, your code must set the Type property of each rdoParameter object to indicate its data type.
   
   
4. Execute the query.

When the query has completed processing, retrieve the output parameter values from the rdoParameters collection.

Coding Output Parameter Stored Procedures

This example executes a stored procedure that expects two input parameters and returns two output parameters along with a return value parameter. Note that the SQL query uses ODBC syntax in the rdoQuery, which is required if you intend to execute stored procedures that return output arguments.

Dim SQL As String, MyOutputVal1 As Variant
Dim MyOutputVal2 As Variant, MyRetValue As Variant _ 
rs As rdoResultset
Dim Ps As rdoQuery

' Use named arguments to open the connection.
Dim cn as New rdoConnection
With cn
   .Connect = "dsn=Sequel;uid=;pwd=;database=workdb"
   .EstablishConnection Prompt:=rdDriverNoPrompt
End With

' Use ODBC parameter argument syntax. 
' Note each argument is identified 
' with a ? character – one for the ReturnValue, 
' one for each of the input arguments 
' and one for each of the output arguments.

SQL = "{? = call TestOutputRS (?, ?, ?, ?) }"

' Create reusable rdoQuery.
Set Ps = cn.CreateQuery("PsTest", SQL)

' Set Parameter "direction" for each output 
' and return value parameter.

Ps(0).Direction = rdParamReturnValue
Ps(3).Direction = rdParamOutput
Ps(4).Direction = rdParamOutput

' Set the input argument values.
Ps.rdoParameters(1) = "Test%"
Ps.rdoParameters(2) = 1


' Create the result set and populate the Ps values.
Set rs = Ps.OpenResultset(rdOpenStatic)

MyRetValue = Ps(0)    ' The return value argument.
MyOutputVal1 = Ps(3) ' The first output parameter.
MyOutputVal2 = Ps(4) ' The second output parameter.