August 31, 2011 at 12:30 pm
I am using SQL Server 2008 R2. I have created 3 stored procedures. Proc1 calls Proc2 which calls Proc3. I begin a transaction in Proc1, but not in Proc2 or Proc3. I have declared @errorcode int OUTPUT in all three procedures. I forced an error in Proc3. I have Commit and Rollback statements in Proc2 depending on the value returned in @errorcode. The output from Proc2 displays the correct value of @errorcode (e.g. 50000). However, the output from Proc1 shows the value of @errorcode = 0. Is there any way to pass the @errorcode value all the way back from Proc3 to Proc1?
Thanks,
Jack
August 31, 2011 at 2:08 pm
I would make sure both the begin transaction and the commit\rollback are in the same proc.
August 31, 2011 at 3:23 pm
Take a look at this example and see if it can help show how to get the value all the way back. This is only 2 levels deep but it is the same concept.
create procedure Proc2
(
@ErrCode int output
) as begin
set @ErrCode = 42
end
go
create procedure Proc1
(
@ErrCode int output
) as begin
exec Proc2 @ErrCode output
end
go
declare @ErrCode int
exec Proc1 @ErrCode output
select @ErrCode
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
August 31, 2011 at 4:02 pm
I see the error in my original post. All of the transactions segments (begin/commit/rollback) are in Proc1. There are NO commits/rollbacks in Proc2.
For example (I should have put this in my original post):
Proc1
CREATE Procedure Proc1 @Key1 int, @ReturnValue int OUTPUT
AS
BEGIN
BEGIN TRANSACTION Trans1
EXEC Proc2 @Key1, @ReturnValue
If @ReturnValue = 0
BEGIN TRY
--Some code goes here to perform an update/insert
COMMIT TRANSACTION Tran1
END TRY
BEGIN CATCH
ROLLBACK TRANSACTION Tran1
END CATCH
Else
BEGIN
ROLLBACK TRANSACTION Tran1
END
END
Proc2
CREATE Procedure Proc2 @Key1 int, @ReturnValue int OUTPUT
AS
BEGIN
Proc3 @Key1, @ReturnValue OUTPUT
END
Proc3
CREATE Procedure Proc3 @Key1 int, @ReturnValue int OUTPUT
AS
BEGIN
SET @ReturnValue = 50000
END
I execute Proc1, enter 1 for @Key1, and leave @ReturnValue empty
The output from Proc2 shows @ReturnValue = 50000
The output from Proc1 shows @ReturnValue = 0
Thanks,
Jack
September 1, 2011 at 7:11 am
You left of output when calling Proc2
EXEC Proc2 @Key1, @ReturnValue OUTPUT
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
September 1, 2011 at 7:56 am
I just missed it when I entered the code in my post. The OUTPUT keyword is there in my actual procedure.
I am leaning towards the following:
Proc1
CREATE Procedure Proc1 @Key1 int, @ReturnValue int OUTPUT
AS
BEGIN
BEGIN TRANSACTION Trans1
EXEC Proc3 @Key1, @ReturnValue OUTPUT
If @ReturnValue = 0
BEGIN
EXEC Proc2 @Key1, @ReturnValue OUTPUT
END
If @ReturnValue = 0
BEGIN TRY
--Some code goes here to perform an update/insert
COMMIT TRANSACTION Tran1
END TRY
BEGIN CATCH
ROLLBACK TRANSACTION Tran1
END CATCH
Else
BEGIN
ROLLBACK TRANSACTION Tran1
END
END
Proc2
CREATE Procedure Proc2 @Key1 int, @ReturnValue int OUTPUT
AS
BEGIN
...Code here for the body of Proc2
END
Proc3
CREATE Procedure Proc3 @Key1 int, @ReturnValue int OUTPUT
AS
BEGIN
SET @ReturnValue = 50000
END
I have done this before without any issues. Since the table processed in Proc3 is a child table of the one processed in Proc2, I thought it would be cleaner to have the call to Proc3 nested.
Thanks,
Jack
September 1, 2011 at 8:25 am
I found my error and everything is working perfectly now. I did not initialize @ReturnValue to 0. When no error occurred, @ReturnValue remained NULL. When I tested for @ReturnValue = 0, the test failed since NULL is not equivalent to 0. Somehow, I had an error in my real code that was causing @ReturnValue to be reset to NULL after an error returned, so it looked as if @ReturnValue was not being returned all the way back to Proc1.
Thanks,
Jack
September 1, 2011 at 8:32 am
Glad you figured out and thanks for letting us know the resolution.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply