Transactions in general

Joachim
CREATE PROCEDURE BigOne
AS
DECLARE @err integer
BEGIN TRANSACTION
EXEC @err = sp1
SELECT @err = coalesce(nullif(@err, 0, @@error)
IF @err <> 0 BEGIN ROLLBACK TRANSACTION RETURN 1 END
EXEC @err = sp2
SELECT @err = coalesce(nullif(@err, 0, @@error)
IF @err <> 0 BEGIN ROLLBACK TRANSACTION RETURN 1 END
EXEC @err = sp3
SELECT @err = coalesce(nullif(@err, 0, @@error)
IF @err <> 0 BEGIN ROLLBACK TRANSACTION RETURN 1 END
EXEC @err = sp4
SELECT @err = coalesce(nullif(@err, 0, @@error)
IF @err <> 0 BEGIN ROLLBACK TRANSACTION RETURN 1 END
COMMIT TRANSACION
GO



"Joachim Hofmann" wrote in message

> Hello,
>
> I have an sp1 which calls sp2 which calls sp3.
> Is it correct that if I only need to surround the code of sp1 with BEGIN /
> END
> TRANSACTION to run all the code in a Transaction?
>
> Thank You
>
> Joachim
 >> Stay informed about: Transactions in general 

I understand the approach of Uri immediately returning the error code
to the caller.
However the short answer "Yes" from Roy does not seem to be correct.


So I did a test script by myself to understand it: sp1 calls sp2, sp2 calls
sp3. Each sp does an INSERT INTO Testtabelle1.
sp3 causes a constraint violation by an INSERT command. But all other INSERTs
remain in spite of a Transaction surrounding all the code in sp1. The @@ERROR
of sp3 is "too far away" from sp1.
>>>>>

use pubs
GO

ALTER PROCEDURE sp1
AS
if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[Testtabelle1]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[Testtabelle1]

CREATE TABLE [dbo].[Testtabelle1] (
[pk] [int] IDENTITY (1, 1) NOT NULL ,
[name] [char] (10) COLLATE Latin1_General_CI_AS NOT NULL
) ON [PRIMARY]


BEGIN transaction t1
INSERT INTO testtabelle1
(name)
VALUES
('von_sp1')
EXECUTE sp2
IF @@ERROR <> 0
BEGIN
/* print 'rollback' */
ROLLBACK transaction t1
END
ELSE
BEGIN
/* print 'commit' */
COMMIT transaction t1
END
GO

ALTER PROCEDURE sp2
AS
INSERT INTO testtabelle1
(name)
VALUES
('von_sp2') -- ok
execute sp3
GO

ALTER PROCEDURE sp3
AS
INSERT INTO testtabelle1
(name)
VALUES
(NULL) -- violation
INSERT INTO testtabelle1
(name)
VALUES
('von_sp3')
GO

<<<<<

Joachim
 >> Stay informed about: Transactions in general 

You need robust error handling. What you are doing now is similar to:

BEGIN TRAN
INSERT
INSERT
INSERT
COMMIT

If INSERT #3 fails, the transaction still commits, so even though the inserts are in the same
transaction, because of lack of error handling, one insert fails and you are left with two performed
operations and one failed operation.

Your checking of @@ERROR after execution p2 doesn't help since you need to capture @@ERROR
immediately after the INSERT inside the inner procedures. So, communicate the failure of the inner
procedures using a return code and have the outer most procedure check against that return code. Or,
investigate the new TRY/CATCH elements available in 2005.



--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/


"Joachim Hofmann" wrote in message

>
> I understand the approach of Uri immediately returning the error code
> to the caller.
> However the short answer "Yes" from Roy does not seem to be correct.
>
>
> So I did a test script by myself to understand it: sp1 calls sp2, sp2 calls
> sp3. Each sp does an INSERT INTO Testtabelle1.
> sp3 causes a constraint violation by an INSERT command. But all other INSERTs
> remain in spite of a Transaction surrounding all the code in sp1. The @@ERROR
> of sp3 is "too far away" from sp1.
> >>>>>
>
> use pubs
> GO
>
> ALTER PROCEDURE sp1
> AS
> if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Testtabelle1]') and
> OBJECTPROPERTY(id, N'IsUserTable') = 1)
> drop table [dbo].[Testtabelle1]
>
> CREATE TABLE [dbo].[Testtabelle1] (
> [pk] [int] IDENTITY (1, 1) NOT NULL ,
> [name] [char] (10) COLLATE Latin1_General_CI_AS NOT NULL
> ) ON [PRIMARY]
>
>
> BEGIN transaction t1
> INSERT INTO testtabelle1
> (name)
> VALUES
> ('von_sp1')
> EXECUTE sp2
> IF @@ERROR <> 0
> BEGIN
> /* print 'rollback' */
> ROLLBACK transaction t1
> END
> ELSE
> BEGIN
> /* print 'commit' */
> COMMIT transaction t1
> END
> GO
>
> ALTER PROCEDURE sp2
> AS
> INSERT INTO testtabelle1
> (name)
> VALUES
> ('von_sp2') -- ok
> execute sp3
> GO
>
> ALTER PROCEDURE sp3
> AS
> INSERT INTO testtabelle1
> (name)
> VALUES
> (NULL) -- violation
> INSERT INTO testtabelle1
> (name)
> VALUES
> ('von_sp3')
> GO
>
> <<<<<
>
> Joachim
 >> Stay informed about: Transactions in general