Call Stored Procedure that returns a RETURN code

Help
1

Does anyone have an example of using “Execute Non Query” to call a stored procedure that uses its Return Code to return a result?

For example, let’s say I have stored proc “TheAnswerToTheUniverse_sproc” defined as

CREATE PROCEDURE TheAnswerToTheUniverse_sproc
AS
RETURN 42

I call it with “Execute Non Query”, attempting to put the Return Code in a variable named “anInt”

image
image2061×627 62.6 KB

But all I get back is “-1” : “The answer to the Universe is -1”

The other posts I’m reading and the documentation seems to suggest that the Affected Records attribute is just for calls that do things like INSERT, DELETE, etc. So if there’s some way to get back the Return Code, I’d love to hear about it.

2

your procedure is wrong for execute non query… maybe use execute scalar…

3

Is there an “Execute Scalar” activity? Where do I get it? In UIPath.Database.Activities, I only see Connect, Disconnect, Execute Non Query, Execute Query, Insert, adn Start Transaction.

image

1 Like
4

would be the one that is not an execute non query :slight_smile:

5

No–don’t think so–Execute Query returns a Datatable. It can be used with anything that gives back a rowset (SELECT … and stored procs that include some sort of SELECT … returns rows and columns). I’m looking for something that can deal with a single value returned from the call.

I know in this case, I could re-write my stored proc to use an OUTPUT parameter and that works since this is just a test/poc thing; however, I have real use cases where there’s a stored proc to call that returns a value and I don’t have the option of changing the code.

6

yes, it can return as SELECT 42, or you can use a return parameter (output).

7

As it’s been 4 years, I guess you already found out how to do it, or either you gave up using Studio.

Could you share your solution, please?