Get Return Value from Stored Procedure

Post any question you may have in regards to GoAnywhere Director and let our talented support staff and other users assist you.
4 posts Page 1 of 1

AmosG

Posts: 4
Joined: Mon Nov 10, 2014 4:49 pm

Post by AmosG » Fri Jan 09, 2015 5:47 pm
I am trying to execute a stored procedure that returns a value. How can I retrieve this value once the stored procedure is executed?

Thanks.

Support_Rick

Support Specialist
Posts: 590
Joined: Tue Jul 17, 2012 2:12 pm
Location: Phoenix, AZ

Post by Support_Rick » Fri Jan 09, 2015 5:57 pm
Amos,

This is one of those areas that is hard to explain sometimes.

If the Stored Procedure returns a Rowset and that's all it does, then this value is usually returned. It takes some testing for this.

Otherwise, if there are numerous commands executed from within the Stored Procedure, then the return value of the 1st command is usually returned, not the final (last) value. If you're having issues getting a value returned, it might be easier to have the Stored Procedure write the desired values out to a Temp Table, then read the Temp Table values on a subsequent Query (within the same SQL Task) to get what you need.
Rick Elliott
Lead Solutions Consultant
(402) 944.4242
(800) 949-4696

AmosG

Posts: 4
Joined: Mon Nov 10, 2014 4:49 pm

Post by AmosG » Mon Jan 12, 2015 6:42 pm
This is what I have. The stored procedure returns a single value. I would like to use the returned value as part of an IF statement.

I was looking for a way to log\print the returned value using the following, before I add the IF logic. I am not getting the desired results.


<sql label="Call DB" resourceId="TestDB" autoCommit="false" version="1.0">
<query label="Call Stored Procedure" outputVariable="TestCount">
<statement>DECLARE @return_value int,
@TSTCOUNTS int

EXEC @return_value = [dbo].[spReturnTSTCount]
@TSTCOUNTS = @TSTCOUNTS OUTPUT

SELECT @TSTCOUNTS as N&apos;@TestCOUNTS&apos;

</statement>
</query>
</sql>
<print label="Test" version="1.0">
<![CDATA[Testing: ${TestCount}]]>
</print>

Support_Rick

Support Specialist
Posts: 590
Joined: Tue Jul 17, 2012 2:12 pm
Location: Phoenix, AZ

Post by Support_Rick » Tue Jan 13, 2015 1:22 pm
GADirector cannot handle OUT variables to return information (values) from a Stored Procedure.
You'll have to write the values into a Temp Table, then select them from that Temp Table to retrieve them to use them inside your Project.
Rick Elliott
Lead Solutions Consultant
(402) 944.4242
(800) 949-4696
4 posts Page 1 of 1