Get Return Value from Stored Procedure

[AmosG]

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]

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.

[AmosG]

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]

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.