stored procedure

[ssingh]

I have a test stored procedure in sql server. For eg

CREATE PROCEDURE SP_test @sum varchar(10) output

As

BEGIN
select @sum = '1000'
end
go

Please let me know the syntax, how do I get its return
value using ADO command object in VB6

[brucevde]

Don't confuse Return Value with Output Values. They are not the same and handled somewhat differently.

A Return Value can only be an integer and must be explicitly set in the stored procedure. Use the Return statement ie, Return 999 (or Return @RetValue when using a local variable).

To get the Return value or Output Value you need to add parameters to the ADO command object. Note - the Return Value parameter must be the first one appended to the collection ( unless you are using NamedParameters) but it can be omitted, if your application does not care about its value.

Here is a sample for your test procedure.

With ADOCommand
   .CommandText = "sp_test"
   .CommandType = adCmdStoredProc
   .ActiveConnection = some ado connection
   .Parameters.Append .CreateParameter("RetVal", adInteger, adParamReturnValue)
   .Parameters.Append .CreateParameter("OutputVal",adVarchar,adParamOutput,10)
   .Execute ,,adExecuteNoRecords 
 
   'or Set Recordset = .Execute - if your stored procedure returns a recordset (this will be a ForwardOnly Readonly cursor).
 
   debug.print .Parameters("RetVal").Value, .Parameters("OutputVal").Value
End With

If you are unsure, you can have ADO populate the parameters for you by doing a .Parameters.Refresh just after the .ActiveConnection line above (and of course omit the parameters.Append lines). Then using the Locals window or debug.print you can see what you need to include for each parameter.

[techyspecy]

Originally posted by brucevde
Don't confuse Return Value with Output Values. They are not the same and handled somewhat differently.

A Return Value can only be an integer and must be explicitly set in the stored procedure. Use the Return statement ie, Return 999 (or Return @RetValue when using a local variable).

To get the Return value or Output Value you need to add parameters to the ADO command object. Note - the Return Value parameter must be the first one appended to the collection ( unless you are using NamedParameters) but it can be omitted, if your application does not care about its value.

Here is a sample for your test procedure.

With ADOCommand
   .CommandText = "sp_test"
   .CommandType = adCmdStoredProc
   .ActiveConnection = some ado connection
   .Parameters.Append .CreateParameter("RetVal", adInteger, adParamReturnValue)
   .Parameters.Append .CreateParameter("OutputVal",adVarchar,adParamOutput,10)
   .Execute ,,adExecuteNoRecords 
 
   'or Set Recordset = .Execute - if your stored procedure returns a recordset (this will be a ForwardOnly Readonly cursor).
 
   debug.print .Parameters("RetVal").Value, .Parameters("OutputVal").Value
End With

If you are unsure, you can have ADO populate the parameters for you by doing a .Parameters.Refresh just after the .ActiveConnection line above (and of course omit the parameters.Append lines). Then using the Locals window or debug.print you can see what you need to include for each parameter.

Or the simpler way ...

Dim lAdoConn As New adodb.connection
    
    ' open the connection
    
Dim lrstAdo As New adodb.Recordset
 
    lAdoConn.SP_test 10, lrstAdo
    
    MsgBox lrstAdo.fields(0)

[techgnome]

Originally posted by techyspecy
Or the simpler way ...

Dim lAdoConn As New adodb.connection
    
    ' open the connection
    
Dim lrstAdo As New adodb.Recordset
 
    lAdoConn.SP_test 10, lrstAdo
    
    MsgBox lrstAdo.fields(0)

Excepting that it's an output parameter..

[ssingh]

thanks a bunch!