Problem Passing a ReturnValue to a Global Variable Using A SQL Task
-
I'm running a store procedure that gets an ID out of a ID control Table and increments it by one then returns the ID as a ReturnValue. I'm using a SQL Task to pass the value as a row value (using Output parameter) and passing it to a global called gvPtJournalID. For whatever reason the global (gvPtJournalID) is not getting updated with the new ReturnValue/ID. The Output parameter sees the "ReturnValue" as a parameter. Any ideas why the global variable ID(gvPtJournalID) isn't getting updated? Below is the code I'm running in the SQL task.
Declare @returnValue int, @id_type char(30)
Exec @ returnValue = dbo.sp_get_id_count 'Journal_ID', 'Server', 'DB', 'Application'
Select @returnvalue AS ReturnValue
-
Landon,
Do you have the variable @returnValue mapped to the global variable gvPTJournalID in the Output Parameter Screen? (left hand column should contain the @returnValue).
-
Yes I do have it mapped to @returnValue. That is why I don't understand why it isn't working. Any suggestions??
-
Actually the output parameter box will only allow me to select ReturnValue. It isn't @ReturnValue and I can't change or select @ReturnValue. ReturnValue is the only choice.(without @)
-
The output parameter would show ReturnValue (without @) because you have selected it that way in your SQL task.
I mean
Select @returnvalue AS ReturnValue
If the following is exactly what you have in SQL task task , it might error
Declare @returnValue int, @id_type char(30)
Exec @ returnValue = dbo.sp_get_id_count 'Journal_ID', 'Server', 'DB', 'Application'
Select @returnvalue AS ReturnValue
because there is a space between @ and returnvalue in the second line
may be you can try doing something like this if you have specified the output parameter
Declare @outNbr INT
EXEC dbo.sp_get_id_count 'Journal_ID', 'Server', 'DB', 'Application' ,@<whatever is the name of out variable in your prc> = @outNbr OUTPUT
SELECT @outNbr as ReturnValue
Regards
Meghana
-
You can't assign a return a value from a stored procedure to a global variable this way. The global variable settings in the parameter screen only work with resultsets returned from a stored procedure. ie: those generated with a select statement.
Just put a SELECT statement at the end of your stored procedure and it will work.
--------------------
Colt 45 - the original point and click interface
-
Phil
You can return a value from a stored procedure to a global variable this way. See below:
SET NOCOUNT ON
DECLARE @ReturnValue int, @OutTest intEXEC @ReturnValue=dbo.spReturnAndOutputTest @OutTest OUTPUTSELECT @ReturnValue AS ReturnValue, @OutTest AS OutTest
-
Well my response was to your original post
So you declare two local SQL variables, call your stored procedure with an output parameter to assign a value to the local SQL variables then select those variables. Then you assign the local SQL variables to the DTS global variables.
Isn't adding a single select statement to the end of the stored procedure a bit cleaner?
Your T-SQL code in the ExecuteSQL task would be a simple single line,
EXEC dbo.sp_get_id_count 'Journal_ID', 'Server', 'DB', 'Application'
The result returned by the final select is then assigned to the DTS global variables in the regular manner.
--------------------
Colt 45 - the original point and click interface
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply