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 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy