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
Henri
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
>
>
|||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[vbcol=seagreen]
> 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...
are[vbcol=seagreen]
transaction
>
>
|||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 = @.@.ERROR
IF @.intErrorCode = 0 BEGIN
-- Capture transaction state before beginning.
SET @.intTranCountOnEntry = @.@.TRANCOUNT
BEGIN TRANSACTION
SET @.intErrorCode = @.@.ERROR
END
-- Only continue if error free.
IF @.intErrorCode = 0 BEGIN
INSERT ...
SET @.intErrorCode = @.@.ERROR
END
IF @.intErrorCode = 0 BEGIN
INSERT ...
SET @.intErrorCode = @.@.ERROR
END
IF @.intErrorCode = 0 BEGIN
UPDATE ...
SET @.intErrorCode = @.@.ERROR
END
IF @.intErrorCode = 0 BEGIN
DELETE ...
SET @.intErrorCode = @.@.ERROR
END
IF @.intErrorCode = 0 BEGIN
INSERT ...
SET @.intErrorCode = @.@.ERROR
END
-- Only commit if transaction initiated
-- and error free.
IF @.@.TRANCOUNT > @.intTranCountOnEntry BEGIN
IF @.intErrorCode = 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
|||Thanks for your help Anthony :-)
"AnthonyThomas" <Anthony.Thomas@.CommerceBank.com> a crit 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 = @.@.ERROR
IF @.intErrorCode = 0 BEGIN
-- Capture transaction state before beginning.
SET @.intTranCountOnEntry = @.@.TRANCOUNT
BEGIN TRANSACTION
SET @.intErrorCode = @.@.ERROR
END
-- Only continue if error free.
IF @.intErrorCode = 0 BEGIN
INSERT ...
SET @.intErrorCode = @.@.ERROR
END
IF @.intErrorCode = 0 BEGIN
INSERT ...
SET @.intErrorCode = @.@.ERROR
END
IF @.intErrorCode = 0 BEGIN
UPDATE ...
SET @.intErrorCode = @.@.ERROR
END
IF @.intErrorCode = 0 BEGIN
DELETE ...
SET @.intErrorCode = @.@.ERROR
END
IF @.intErrorCode = 0 BEGIN
INSERT ...
SET @.intErrorCode = @.@.ERROR
END
-- Only commit if transaction initiated
-- and error free.
IF @.@.TRANCOUNT > @.intTranCountOnEntry BEGIN
IF @.intErrorCode = 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