Forum Discussion

HalfADimePlural's avatar
HalfADimePlural
New Contributor
5 years ago
Solved

Get return value from a stored procedure

I followed the documentation on stored procedures here and I'm able to get my stored procedure to run, but my return value is always 0. I've searched high and low for my problem but no one else seems...
  • RUDOLF_BOTHMA's avatar
    RUDOLF_BOTHMA
    5 years ago

    VERY old post, but the principals are still the same

     

    Multiple Recordset Stored Procedure

     

    Specifically:

    In ADO however when return multiple recordsets you loop through the
    recordet something like this

    SET Rst = Connection.Execute(procMultipleRst)

    doWhatever1 Rst

    SET Rst = Rst.NextRecordset
    doWhatever2 Rst

    SET Rst = Rst.NextRecordset
    doWhatever3 Rst

RUDOLF_BOTHMA's avatar
RUDOLF_BOTHMA
Community Hero
5 years ago

What does you SP look like.  Your sp may actually be returning the result of 0.  It could be because you don't have nocount on or the like.  If you do, the sp returns two "datasets" and ADO picks up the first one which is just your result

 

Edit:  If this is the case, there are two options off the top of my head:

1. Add SET NOCOUNT ON and off to the SP

2. Allocate the output to a new recordset using sproc.NextRecordset() and use this for your checks/lookups etc.

  • HalfADimePlural's avatar
    HalfADimePlural
    New Contributor
    5 years ago

    I'm not the one who made the stored procedure and I don't have enough experience with them to be sure how it was setup. How would I access the second dataset to get the value I'm looking for? Editing the sproc is not an option.

    • RUDOLF_BOTHMA's avatar
      RUDOLF_BOTHMA
      Community Hero
      5 years ago

      VERY old post, but the principals are still the same

       

      Multiple Recordset Stored Procedure

       

      Specifically:

      In ADO however when return multiple recordsets you loop through the
      recordet something like this

      SET Rst = Connection.Execute(procMultipleRst)

      doWhatever1 Rst

      SET Rst = Rst.NextRecordset
      doWhatever2 Rst

      SET Rst = Rst.NextRecordset
      doWhatever3 Rst