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
- Support Specialist
- Posts: 590
- Joined: Tue Jul 17, 2012 2:12 pm
- Location: Phoenix, AZ
-
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.
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
Lead Solutions Consultant
(402) 944.4242
(800) 949-4696
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'@TestCOUNTS'
</statement>
</query>
</sql>
<print label="Test" version="1.0">
<![CDATA[Testing: ${TestCount}]]>
</print>
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'@TestCOUNTS'
</statement>
</query>
</sql>
<print label="Test" version="1.0">
<![CDATA[Testing: ${TestCount}]]>
</print>
- Support Specialist
- Posts: 590
- Joined: Tue Jul 17, 2012 2:12 pm
- Location: Phoenix, AZ
-
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.
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
Lead Solutions Consultant
(402) 944.4242
(800) 949-4696
4 posts
Page 1 of 1