nested SP follow up question

Sorry, I reposted this as a new thread so it wouldnt be overlooked as a
closed. Here it is...
Thank you for the response. I don't fully understand.
In my example, I wanted to be able to call a ChildSP directly, or call a
ParentSP which calls the ChildSP, and if an error occurs in the child,
everything gets rolled back. Your exaple only included one stored proc, so I
was a little unclear.
I tried to create a 2 SP example using your style. In your error handler,
you check to see if @.@.TranCount > 0. If so, you know that there was an error
above. Howver, if we take this approach in the ChildSP, performing a
Rollback would cause the @.@.TranCount to be set to zero, and when you return
to the ParentSP, we find that @.@.TranCount is now 0, but it was 1 perform we
called ChildSP, and so we ge the error:
Server: Msg 50000, Level 16, State 1, Procedure ChildSP, Line 10
an error was raised
Server: Msg 266, Level 16, State 2, Procedure ChildSP, Line 26
Transaction count after EXECUTE indicates that a COMMIT or ROLLBACK
TRANSACTION statement is missing. Previous count = 1, current count = 0.
Try running the code below.
I would be very much indebted if you could take the 2 SP example and modify
it to a approach that works and is sane.
CREATE TABLE [dbo].[Table1] (
[col1] [int] NULL ,
[col2] [int] NULL
) ON [PRIMARY]
CREATE procedure ParentSP
as
begin
DECLARE @._ERROR INT
BEGIN TRANSACTION
exec @._ERROR = ChildSP 1
SELECT @._ERROR = @.@.ERROR
IF @._ERROR != 0 GOTO ERROR
COMMIT TRANSACTION
RETURN 0
ERROR:
IF @.@.TRANCOUNT > 0 ROLLBACK TRANSACTION
RETURN @._ERROR
end
CREATE procedure ChildSP
(@.RaiseError bit)
as
begin
DECLARE @._ERROR INT
BEGIN TRANSACTION
if (@.RaiseError = 1)
RAISERROR ('an error was raised', 16, 1)
ELSE
UPDATE table1 set col1 = 1
SELECT @._ERROR = @.@.ERROR
IF @._ERROR != 0 GOTO ERROR
UPDATE table1 set col2 = 2
SELECT @._ERROR = @.@.ERROR
IF @._ERROR != 0 GOTO ERROR
COMMIT TRANSACTION
RETURN 0
ERROR:
IF @.@.TRANCOUNT > 0 ROLLBACK TRANSACTION
RETURN @._ERROR
end
"Brian Selzer" <BrianSelzer@.discussions.microsoft.com> wrote in message
news:97330484-71EA-4142-9B25-DAE902EA8836@.microsoft.com...
> There's a few things you should know:
> First: always check for errors after each DML statement or SP call within
> a
> transaction, because it is possible for an early DML statement to fail,
> and
> later ones to pass which causes an insidious data consistency bug that is
> extremely difficult to find. Here's what I do:
> DECLARE @._ERROR INT
> BEGIN TRANSACTION
> UPDATE t1 set col1 = @.col1
> SELECT @._ERROR = @.@.ERROR
> IF @._ERROR != 0 GOTO ERROR
> UPDATE t2 set col2 = @.col2
> SELECT @._ERROR = @.@.ERROR
> IF @._ERROR != 0 GOTO ERROR
> COMMIT TRANSACTION
> RETURN 0
> ERROR:
> IF @.@.TRANCOUNT > 0 ROLLBACK TRANSACTION
> RETURN @._ERROR
> This approach should make your inquiry moot, since @.@.ERROR is set on exit
> from a procedure if @.@.TRANCOUNT is less than what it was upon entry.
>
> I only use save points if I want to roll back only part of a transaction,
> here's what I do:
> DECLARE @._TRANCOUNT INT SET @._TRANCOUNT = @.@.TRANCOUNT
> DECLARE @._ERROR INT
> IF @._TRANCOUNT = 0
> BEGIN TRANSACTION savePoint
> ELSE
> SAVE TRANSACTION savePoint
> UPDATE t1 set col1 = @.col1
> SELECT @._ERROR = @.@.ERROR
> IF @._ERROR != 0 GOTO ERROR
> UPDATE t2 set col2 = @.col2
> SELECT @._ERROR = @.@.ERROR
> IF @._ERROR != 0 GOTO ERROR
> IF @._TRANCOUNT = 0
> COMMIT TRANSACTION savePoint
> RETURN 0
> ERROR:
> IF @.@.TRANCOUNT > @._TRANCOUNT
> ROLLBACK TRANSACTION savePoint
> RETURN @._ERROR
>
>
> "Chad" wrote:
>The code I provided will work when called directly or from another stored
procedure. Use it as a template for both the parent and the child
procedure--in fact use this mechanism in all of your procedures.
You should declare an additional variable, @.RC, in the parent procedure to
receive the return code from the stored procedure call. Otherwise you will
lose the error code that originally caused the failure, for example:
DECLARE @.RC INT, @._ERROR INT
EXEC @.RC = ChildProc
SET @._ERROR = @.@.ERROR
IF @.RC != 0 OR @._ERROR != 0 GOTO ERROR
The key to this approach is that any error, regardless of the reason
(Constraint violation, out of memory, Deadlock victim, etc.) is detected and
handled immediately after it occurs, and the error handling code rolls back
the transaction. When an error occurs in the child procedure, it rolls back
any pending transaction and returns the error code to the caller. The paren
t
procedure detects that an error occurred by examining the return code, and
transferrs control to its own error handler. Since the transaction had
already been rolled back in the child procedure, @.@.TRANCOUNT is zero and thu
s
a rollback in the parent's error handler would cause an additional error.
The condition IF @.@.TRANCOUNT > 0 prevents this. (It also prevents an
additional error in the event the procedure is chosen as a deadlock victim.)
I often extend this mechanism to detect concurrency issues. For example:
CREATE PROCEDURE UpdateT1 (@.col1 int, @.key int, @.version rowversion OUTPUT)
AS
BEGIN
DECLARE @._ERROR INT, @._ROWCOUNT INT
BEGIN TRANSACTION
UPDATE t1 SET col1 = @.col1 where key1 = @.key and ver1 = @.version
SELECT @._ERROR = @.@.ERROR, @._ROWCOUNT = @.@.ROWCOUNT
IF @._ERROR != 0 OR @._ROWCOUNT = 0 GOTO ERROR
SELECT @.version = ver1 FROM t1 WHERE key1 = @.key
SELECT @._ERROR = @.@.ERROR, @._ROWCOUNT = @.@.ROWCOUNT
IF @._ERROR != 0 OR @._ROWCOUNT = 0 GOTO ERROR
COMMIT TRANSACTION
RETURN 0
ERROR:
IF @.@.TRANCOUNT > 0 ROLLBACK TRANSACTION
IF @._ERROR = 0 AND @._ROWCOUNT = 0
RETURN -1 -- indicate that a record was changed by another
user
ELSE
RETURN @._ERROR
END
ver1 is a rowversion (timestamp) column, which is changed any time a record
is changed. If another user changes the record after the time it was read,
then ver1 will be different than @.version, the update statement will not
affect any rows, and consequently @.@.ROWCOUNT will be zero.
"Chad" wrote:

> Sorry, I reposted this as a new thread so it wouldnt be overlooked as a
> closed. Here it is...
> Thank you for the response. I don't fully understand.
> In my example, I wanted to be able to call a ChildSP directly, or call a
> ParentSP which calls the ChildSP, and if an error occurs in the child,
> everything gets rolled back. Your exaple only included one stored proc, so
I
> was a little unclear.
> I tried to create a 2 SP example using your style. In your error handler,
> you check to see if @.@.TranCount > 0. If so, you know that there was an err
or
> above. Howver, if we take this approach in the ChildSP, performing a
> Rollback would cause the @.@.TranCount to be set to zero, and when you retur
n
> to the ParentSP, we find that @.@.TranCount is now 0, but it was 1 perform w
e
> called ChildSP, and so we ge the error:
> Server: Msg 50000, Level 16, State 1, Procedure ChildSP, Line 10
> an error was raised
> Server: Msg 266, Level 16, State 2, Procedure ChildSP, Line 26
> Transaction count after EXECUTE indicates that a COMMIT or ROLLBACK
> TRANSACTION statement is missing. Previous count = 1, current count = 0.
> Try running the code below.
> I would be very much indebted if you could take the 2 SP example and modif
y
> it to a approach that works and is sane.
>
> CREATE TABLE [dbo].[Table1] (
> [col1] [int] NULL ,
> [col2] [int] NULL
> ) ON [PRIMARY]
>
> CREATE procedure ParentSP
> as
> begin
> DECLARE @._ERROR INT
> BEGIN TRANSACTION
> exec @._ERROR = ChildSP 1
> SELECT @._ERROR = @.@.ERROR
> IF @._ERROR != 0 GOTO ERROR
> COMMIT TRANSACTION
> RETURN 0
> ERROR:
> IF @.@.TRANCOUNT > 0 ROLLBACK TRANSACTION
> RETURN @._ERROR
> end
>
> CREATE procedure ChildSP
> (@.RaiseError bit)
> as
> begin
> DECLARE @._ERROR INT
> BEGIN TRANSACTION
> if (@.RaiseError = 1)
> RAISERROR ('an error was raised', 16, 1)
> ELSE
> UPDATE table1 set col1 = 1
>
> SELECT @._ERROR = @.@.ERROR
> IF @._ERROR != 0 GOTO ERROR
> UPDATE table1 set col2 = 2
> SELECT @._ERROR = @.@.ERROR
> IF @._ERROR != 0 GOTO ERROR
> COMMIT TRANSACTION
> RETURN 0
> ERROR:
> IF @.@.TRANCOUNT > 0 ROLLBACK TRANSACTION
> RETURN @._ERROR
> end
>
> "Brian Selzer" <BrianSelzer@.discussions.microsoft.com> wrote in message
> news:97330484-71EA-4142-9B25-DAE902EA8836@.microsoft.com...
>
>