newbie question about transactions

I'm thinking of using transactions but there's something I don't know.
Consider that kind of code:
BEGIN TRANS
INSERT ...
INSERT ...
UPDATE ...
DELETE ...
INSERT...
COMMIT
My question is:
do I have to write after *each* insert, update or delete
IF @.@.ERROR <> 0 BEGIN
ROLLBACK
RETURN
END
for my procedure to work well?
It's not a big deal if there are only 2 or 3 operations, but if there are
lots of them...
Can you tell me what the minimum code is for a procedure using transaction
to be valid?
Thanks
HenriHi
You have to check after each statement as the @.@.error variable gets reset
every time. In your example, you need the error handler 7 times, once after
each statement (you could get away with 6, excluding the BEGIN TRAN as you
should not error on that).
SQL Server 2005 brings structured exception handling, but until then, that
is the only way.
--
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Henri" <hmfireball@.hotmail.com> wrote in message
news:eC9loV10EHA.3484@.TK2MSFTNGP09.phx.gbl...
> I'm thinking of using transactions but there's something I don't know.
> Consider that kind of code:
> BEGIN TRANS
> INSERT ...
> INSERT ...
> UPDATE ...
> DELETE ...
> INSERT...
> COMMIT
> My question is:
> do I have to write after *each* insert, update or delete
> IF @.@.ERROR <> 0 BEGIN
> ROLLBACK
> RETURN
> END
> for my procedure to work well?
> It's not a big deal if there are only 2 or 3 operations, but if there are
> lots of them...
> Can you tell me what the minimum code is for a procedure using transaction
> to be valid?
> Thanks
> Henri
>
>|||Thanks a lot for your answer Mike :-)
Henri
"Mike Epprecht (SQL MVP)" <mike@.epprecht.net> a écrit dans le message de
news:eIDrTa20EHA.1932@.TK2MSFTNGP09.phx.gbl...
> Hi
> You have to check after each statement as the @.@.error variable gets reset
> every time. In your example, you need the error handler 7 times, once
after
> each statement (you could get away with 6, excluding the BEGIN TRAN as you
> should not error on that).
> SQL Server 2005 brings structured exception handling, but until then, that
> is the only way.
> --
> --
> Mike Epprecht, Microsoft SQL Server MVP
> Zurich, Switzerland
> IM: mike@.epprecht.net
> MVP Program: http://www.microsoft.com/mvp
> Blog: http://www.msmvps.com/epprecht/
> "Henri" <hmfireball@.hotmail.com> wrote in message
> news:eC9loV10EHA.3484@.TK2MSFTNGP09.phx.gbl...
> > I'm thinking of using transactions but there's something I don't know.
> > Consider that kind of code:
> >
> > BEGIN TRANS
> >
> > INSERT ...
> > INSERT ...
> > UPDATE ...
> > DELETE ...
> > INSERT...
> >
> > COMMIT
> >
> > My question is:
> > do I have to write after *each* insert, update or delete
> > IF @.@.ERROR <> 0 BEGIN
> > ROLLBACK
> > RETURN
> > END
> >
> > for my procedure to work well?
> >
> > It's not a big deal if there are only 2 or 3 operations, but if there
are
> > lots of them...
> > Can you tell me what the minimum code is for a procedure using
transaction
> > to be valid?
> > Thanks
> >
> > Henri
> >
> >
> >
>
>|||This is a multi-part message in MIME format.
--=_NextPart_000_0015_01C4D4EB.600E75E0
Content-Type: text/plain;
charset="Windows-1252"
Content-Transfer-Encoding: quoted-printable
Thanks for your help Anthony :-)
"AnthonyThomas" <Anthony.Thomas@.CommerceBank.com> a =E9crit dans le =message de news:O7n%23T9K1EHA.1076@.TK2MSFTNGP09.phx.gbl...
I like to defer my exception handling and sometimes have inline =conditions. The following is a general layout that I use, but the one =you describe is typical as well.
CREATE PROCEDURE DataModificationTransaction1
@.Param1 AS DataType1
,@.Param2 AS DataType2
...
,@.ParamN AS DataTypeN
AS
/*
**
** Procedure Information Comment Block
** */
DECLARE @.intTranCountOnEntry AS INT
,@.intErrorCode AS INT
-- Environment Configuration.
SET XACT_ABORT OFF
SET IMPLICIT_TRANSACTIONS OFF
SET NOCOUNT ON
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
-- Variable Initialization.
SET @.intErrorCode =3D @.@.ERROR
IF @.intErrorCode =3D 0 BEGIN
-- Capture transaction state before beginning.
SET @.intTranCountOnEntry =3D @.@.TRANCOUNT
BEGIN TRANSACTION
SET @.intErrorCode =3D @.@.ERROR
END
-- Only continue if error free.
IF @.intErrorCode =3D 0 BEGIN
INSERT ...
SET @.intErrorCode =3D @.@.ERROR
END
IF @.intErrorCode =3D 0 BEGIN
INSERT ...
SET @.intErrorCode =3D @.@.ERROR
END
IF @.intErrorCode =3D 0 BEGIN
UPDATE ...
SET @.intErrorCode =3D @.@.ERROR
END
IF @.intErrorCode =3D 0 BEGIN
DELETE ...
SET @.intErrorCode =3D @.@.ERROR
END
IF @.intErrorCode =3D 0 BEGIN
INSERT ...
SET @.intErrorCode =3D @.@.ERROR
END
-- Only commit if transaction initiated
-- and error free.
IF @.@.TRANCOUNT > @.intTranCountOnEntry BEGIN
IF @.intErrorCode =3D 0 BEGIN
COMMIT TRANSACTION
END
ELSE BEGIN
ROLLBACK TRANSACTION
END
END
RETURN @.intErrorCode
You can also nest the conditional statements but you MUST check the =status of @.@.ERROR after each DML statement if you wish to properly trap =errors. Also, it is VERY important that you initialize the appropriate =environmental parameters on code launch since transactions are highly =sensitive to these settings. Being explicit will help you in any =debugging situations.
Hope this helps.
Sincerely,
Anthony Thomas
-- "Henri" <hmfireball@.hotmail.com> wrote in message =news:eC9loV10EHA.3484@.TK2MSFTNGP09.phx.gbl...
I'm thinking of using transactions but there's something I don't =know.
Consider that kind of code:
BEGIN TRANS
INSERT ...
INSERT ...
UPDATE ...
DELETE ...
INSERT...
COMMIT
My question is:
do I have to write after *each* insert, update or delete
IF @.@.ERROR <> 0 BEGIN
ROLLBACK
RETURN
END
for my procedure to work well?
It's not a big deal if there are only 2 or 3 operations, but if =there are
lots of them...
Can you tell me what the minimum code is for a procedure using =transaction
to be valid?
Thanks
Henri
--=_NextPart_000_0015_01C4D4EB.600E75E0
Content-Type: text/html;
charset="Windows-1252"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
&

---

Thanks for your help Anthony =:-)
"AnthonyThomas" a =E9crit dans le message de news:O7n%23T9K1EHA.=1076@.TK2MSFTNGP09.phx.gbl...
I like =to defer my exception handling and sometimes have inline conditions. The =following is a general layout that I use, but the one you describe is typical as = well.

CREATE =PROCEDURE DataModificationTransaction1 @.Param1 AS =DataType1 ,@.Param2 AS DataType2 ... ,@.ParamN AS DataTypeN AS

/***** =Procedure Information Comment Block** */

DECLARE @.intTranCountOnEntry AS INT ,@.intErrorCode AS =INT -- Environment Configuration.SET XACT_ABORT OFFSET =IMPLICIT_TRANSACTIONS OFFSET NOCOUNT ONSET TRANSACTION ISOLATION LEVEL SERIALIZABLE

-- Variable Initialization.SET @.intErrorCode =3D @.@.ERROR

IF =@.intErrorCode =3D 0 BEGIN -- Capture transaction state before =beginning. SET @.intTranCountOnEntry =3D @.@.TRANCOUNT BEGIN =TRANSACTION SET @.intErrorCode =3D @.@.ERROR END -- Only continue =if error free.IF @.intErrorCode =3D 0 BEGIN INSERT ... SET = @.intErrorCode =3D @.@.ERROR END IF @.intErrorCode ==3D 0 BEGIN INSERT ... SET @.intErrorCode =3D @.@.ERROR END IF @.intErrorCode =3D 0 =BEGIN UPDATE ... SET @.intErrorCode =3D =@.@.ERROR END IF @.intErrorCode =3D 0 BEGIN DELETE ... SET =@.intErrorCode =3D @.@.ERROR END IF @.intErrorCode =3D 0 =BEGIN INSERT ... SET @.intErrorCode =3D =@.@.ERROR END -- Only commit if transaction initiated-- and error free.IF =@.@.TRANCOUNT > @.intTranCountOnEntry BEGIN IF @.intErrorCode =3D 0 BEGIN COMMIT TRANSACTION END ELSE BEGIN ROLLBACK =TRANSACTION END END =RETURN @.intErrorCode
You can =also nest the conditional statements but you MUST check the status of @.@.ERROR after =each DML statement if you wish to properly trap errors. Also, it is VERY important that you initialize the appropriate environmental parameters =on code launch since transactions are highly sensitive to these =settings. Being explicit will help you in any debugging situations.

Hope =this helps.

Sincerely,


Anthony = Thomas



--
"Henri" =wrote in message news:eC9loV10EHA.3484=@.TK2MSFTNGP09.phx.gbl...I'm thinking of using transactions but there's something I don't know.Consider that kind of code:BEGIN =TRANSINSERT ...INSERT ...UPDATE ...DELETE ...INSERT...COMMITMy question is:do I have =to write after *each* insert, update or deleteIF @.@.ERROR 0 = BEGIN ROLLBACK RETURNENDfor my =procedure to work well?It's not a big deal if there are only 2 or 3 =operations, but if there arelots of them...Can you tell me what the =minimum code is for a procedure using transactionto be =valid?ThanksHenri

---

--=_NextPart_000_0015_01C4D4EB.600E75E0--