Return result set and return value/output parameter

Return result set and return value/output parameter

Post by Robert Stricklan » Tue, 07 Jan 2003 03:33:33

I have the following proc ( doesn't do much but return result set , return
value, and output parameter):

-- Creates the procedure
alter procedure sp_JobsTest

-- declare three parameter variables


FROM job_title_code

The vb code uses command object to load and execute. Check following:

Dim oConnection         As ADODB.Connection
Dim oCommand            As ADODB.Command
Dim oRS                 As ADODB.Recordset
Dim sSQL                As String

Dim iVal
Dim oVal

    iVal = 1
    oVal = 1

  '-- Create a command object --
  Set oCommand = New ADODB.Command

  '-- Make an ODBC connection to the (local) SQL server,
  '-- connecting to the Pubs database with the default sa login and empty
  oCommand.ActiveConnection = "Provider=SQLOLEDB;Data
Source=Development;Initial Catalog=TimeConsultant;User
Id=sa;Password=;Connect Timeout=10"

  '-- define the name of the command
  oCommand.CommandText = "sp_JobsTest"

  '-- define the type of the command as a stored procedure (numeric value =
  oCommand.CommandType = adCmdStoredProc

  oCommand.Parameters.Append oCommand.CreateParameter("RETURN_VALUE",
adInteger, adParamReturnValue, 4)

adParamInput, , iVal)

adInteger, adParamOutput, oVal)

  '-- execute the command
  Set oRS = oCommand.Execute

  MsgBox oCommand.Parameters(0).Value

When I run vb code, return code is blank. If I remove the select from the
stored procedure, I get the return value. What settings I'm is missing.



Return result set and return value/output parameter

Post by Luke Zhang [M » Tue, 07 Jan 2003 20:50:01

To get the return value, you must close the recordset first, for example:

Set oRS = oCommand.Execute
MsgBox oCommand.Parameters(0).Value


(This posting is provided "AS IS", with no warranties, and confers no


1. Stored procedure returning empty output parameters or return value

I have a stored procedure that when passed a username I want to call
sp_droplogin. If this call fails (user or aliased in a database)
return error value -1. If successful return 1.

I can get it to work using sql oledb whether it fails or not, but not
using a ODBC DSN. If it is successful I get 1 returned if it fails I
get nothing at all. I have to use the DSN approach so I need to figure
out what's going on.

I've tried using both a output parameter and a return value with the
same results.

I'm developing in VB 6, SQL 7, MDAC 2.6

Also, even when I set a default value for the output parameter/return
value before I call sp_droplogin, it gets wiped out and nothing is

Any help would be appreciated.


2. Up for a challenge?

3. How to capture the return value and output parameter value of a stored procedure in VB

4. Oracle Applications Administrator

5. Return XML result set from sp OUTPUT param

6. truncate time

7. Return values/output parameters from an anonymous block of t-sql

8. Sending E-Mail

9. Output parameter not returning value?

10. Returning value of COMPUTE as OUTPUT parameter

11. JDBC: return values and output parameters from stored procedures

12. Can't pass return value or output parameter to ADO if storeproc updates

13. Return Values and Output parameters of stored proc