cancel
Showing results for 
Search instead for 
Did you mean: 

Get return value from a stored procedure

SOLVED
New Contributor

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 to be having it.

 

Set sproc = ADO.CreateADOStoredProc
sproc.ConnectionString = Project.Variables.ConnStringDB02
sproc.ProcedureName = WMSLib.useDB & ".dbo.get_TestLabel"
  
Call sproc.Parameters.AddParameter()
sproc.Parameters.Items(0).Name = "RETURN_VALUE"
sproc.Parameters.Items(0).DataType = adInteger
sproc.Parameters.Items(0).Direction = adParamReturnValue
sproc.Parameters.Items(0).Value = Null
Call sproc.ExecProc()

'this always ends up being zero
labelNum = sproc.Parameters.Items(0).Value
1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Community Leader

Re: Get return value from a stored procedure

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


-------------------------------------------------
Standard syntax disclaimers apply
Regards,
3 REPLIES 3
Community Leader

Re: Get return value from a stored procedure

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.


-------------------------------------------------
Standard syntax disclaimers apply
Regards,
New Contributor

Re: Get return value from a stored procedure

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.

Highlighted
Community Leader

Re: Get return value from a stored procedure

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


-------------------------------------------------
Standard syntax disclaimers apply
Regards,
New Here?
Join us and watch the welcome video:
6 / 7 API Testing Mistakes Video
Top Kudoed Authors