I'm slightly confused about an issue I am having in getting a return value
from a stored procedure into a global variable. I have one stored procedure
that simply returns a calculated value based on an argument passed to it.
In my DTS package, I create an Execute SQL job like this:

Declare @retval Int
Exec @retval = sp_returncalcvalue 15
Select @retval rt

In the parameter mapping window, I can map rt to a global variable I've
created, and the process works as intended. The global variable stores the
result of the calculation.

But I have another stored procedure which inserts a record into a table
containing an identity field and then uses a select to retrieve the value of
the newly inserted identity field. I want the stored procedure to RETURN
this value so I can store it in a global variable. I use code in an Execute
SQL task just like the example above, and I have the parameter mapping set
up, but when I execute the step, the global variable remains unchanged, and
I don't understand why. I have been able to verify that the stored proc
returns the correct value, and I have modified the settings for the
transaction isolation level in my package (to no avail.)

The difference between the two stored procedures is that one simply makes a
calculation, while the other executes a data modification. I'd appreciate
any help understanding why I can't get the return value from the second
stored proc into a global variable. Thanks in advance!

In article <O2PE1$bjDHA.1096 (AT) TK2MSFTNGP11 (DOT) phx.gbl>, Greg Druian
<greg.druian (AT) gbrx (DOT) com> writes
Your terminology is confusing as a stored procedure return value is one
that you supply to the RETURN statement. You are using a SELECT
statement to present the result.

Either way the problem is due to the additional row set operation you
have with the INSERT. When you run an insert or similar in Query
Analyzer you see a message "x rows affected" or similar. This
information message actually appears as a result set to DTS (and also
ADO), which means that your SELECT is now the second result set. Simple
way to get around this is to add the SET NOCOUNT ON command to the top
of the stored procedure code.

--
Darren Green (SQL Server MVP)
DTS - http://www.sqldts.com

PASS - the definitive, global community for SQL Server professionals
http://www.sqlpass.org

Sorry about the confusion regarding the Select and Return: the Select grabs
the value which I then return: Return @retval. I've applied the Set Nocount
On and it makes the process work correctly. Thanks very much for this
insight!

"Darren Green" <darren.green (AT) reply-to-newsgroup-only (DOT) uk.com> wrote in
message news:Xv9hyNDEUFh$Ewda (AT) sqldts (DOT) com...