Output Parameter returning empty string

[jpatchak]

OK, not sure if there is a way around this. I have some relatively complicated SQL code in a stored proc. The proc is working fine. When I execute this code in SSMS, it returns a record set and an output parameter (varchar(50):

Declare @name varchar(50)
exec pra_StoredProc
    @id=12,
    @name=@name OUTPUT
select @name

So, I need to access this data from Microsoft Access. I need the proc to return a recordset that I can work with and I also need the output parameter value. However, when I do this in VBA:

 
Dim myrst As New ADODB.Recordset
Dim mycmd As New ADODB.Command
Dim mycon As New ADODB.Connection
 
mycon.open connectionString
 
With mycmd
    .ActiveConnection = mycon
    .CommandText = "pra_StoredProc"
    .CommandType = adCmdStoredProc
    .Parameters.Append .CreateParameter("@id", adInteger, adParamInput, , 12)
    .Parameters.Append .CreateParameter("@name", adVarChar, adParamOutput, 50)
    Set myrst = .Execute
    MsgBox .Parameters("@name")
End With
 

The msgbox displays an empty string. The problem I know is the line that says:

Set myrst = .Execute

If I change it to just:

.Execute

it returns the right value for the output parameter. However, I also need to work with the recordset returned by the proc, which is why I was setting the myrst object reference to mycmd.execute. Is there any way to get both the data in the recordset returned by the stored proc into an ADODB Recordset AND the output parameter's value into a string variable (besides executing the proc twice)?

[jpatchak]

Hmmm, no takers huh? I am just going to execute the proc twice, but this makes me a little worried, as I am trying to write a procedure that can handle all kinds of these procedures and some of them are going to take 15+ seconds to execute (per execution). Not crazy about that.

[MMcCarthy]

Can you explain more about what value is being returned by the stored proc. Is it just one record and one field and if so why are you putting it in a recordset variable.

[jpatchak]

The proc constructs a select statement and executes it. It returns the results of the select along with the scalar value in the output parameter. The last 2 statements would be something like this (the @sql variable contains the text of the select statement):

@name = 'The title of a graph'
exec(@sql)
 

The purpose of the vb code is to take the dataset returned by the proc and dump it into an excel sheet (nicely formatted) and automatically generate a corresponding chart. I want to be able to use this same code for all sorts of these same kind of procs - the dataset becoming the data for the excel range and the scalar value from the output parameter becoming the title of the graph.

[MMcCarthy]

OK this is not the way I would have done it but I've done some research and this appears to be how it works.

Dim myCon As ADODB.Connection  
Dim myCmd As ADODB.Command 
Dim myPara1 As ADODB.Parameter
Dim myPara2 As ADODB.Parameter
Dim myRst As ADODB.Recordset 
 
    Set myCon = New ADODB.Connection
    Set myCmd = New ADODB.Command 
 
    myCon.ConnectionString = "connectionString"
    myCon.Open myCon.ConnectionString
 
    With mycmd
        .ActiveConnection = myCon.ConnectionString
        .CommandText = "pra_StoredProc" 
        .CommandType = adCmdStoredProc 
    End With
 
    'Create 2 output parameters
    Set myPara1 = myCmd.CreateParameter("id", adInteger, adParamOutput, , 12)
    Set mypara2 = myCmd.CreateParameter("name", adVarChar, adParamOutput, 50)
 
    'Append the output parameters to command object
    myCmd.Parameters.Append myPara1
    myCmd.Parameters.Append mypara2
 
    Set myRst = myCmd.Execute 
 
    'Add your code here
 
    'close the objects
    myRst.Close
    myCon.Close
 
    'reset the objects
    Set myCmd = Nothing
    Set myCon = Nothing
    Set myPara1 = Nothing
    Set mypara2 = Nothing
    Set myRst = Nothing