SQL stored procedures that return both a recordset AND a return value and VB

[cyberia]

Hello everyone,

I have probably spent way too much time on this already. If you guys and gals can't help, I'll move on and redesign my stored procedures.

I am using MS SQL Server 7.0 and VB6. I have a stored procedure that returns both a recordset AND a return value. I execute the stored procedure from a DLL that is called from an ASP script.

The problem is that the return value cannot be read before the recordset is closed. However, I need to read the return value in the DLL before I pass the recordset to the ASP script.

How can I read the return value without closing the recordset?

Thanks for any help.

 

[cyberia]

I think I found the answer:

I combined 2 workarounds, neither of which provided a complete solution: I use a client-side cursor and discard the recordset if the return value indicates an error.

 

[MonkeyK]

cyberia,
I had a hard time figuring this out too. Here is how I did it:

The stored proc has to explicitely return an error, at the end put something like
IF @@ERROR <> 0
RETURN @@ERROR

Then the VB code needs a return parm who's value you can get, so

'VB Code
Set lprmReturn = lobjCmd.CreateParameter("Ret Code", _
adInteger, _
adParamReturnValue)
lobjCmd.Parameters.Append lprmReturn

'...

Set lrecListing = lobjCmd.Execute()
Set lrecListing.ActiveConnection = Nothing
If lprmReturn.Value = 0 Then
Set function = lrecListing
Else
Err.Raise -5000, "", "Error calling SP" & lprmReturn.Value
End If


Hope this helps,
Mike

 

[cyberia]

Thanks for the reply MonkeyK, that's exactly what I do. If the SP returns 1 (error), I close the recordset, and then the RAISERROR info is available to my VB code, otherwise I return the recordset (as there was no error) from my VB function.