automatic or manual ROLLBACK

Hi,
I have a job that executes the TSQL below. Do I need to do the
errorchecking myself (as below) or does the transaction automaticaly
rollback if a statement fails?
Feel free to comment on the script. (Its never too late to learn).
Yours sincerely,
Jo
SET NOCOUNT ON
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
DECLARE @.MYERROR
BEGIN TRANSACTION
INSERT INTO sequencetoolingbak
(
orderid,
partno,
jobno,
deliverydate,
time_of_creation
)
SELECT orderid, partno, jobno, deliverydate, time_of_creation
FROM dbo.sequencetooling AS seq
WHERE deliverydate < DATEADD(d, -14, GETDATE())
AND NOT EXISTS
(
SELECT orderid
FROM dbo.sequencetoolingbak AS bak
WHERE bak.orderid = seq.orderid
)
SET @.MYERROR = @.@.ERROR
IF @.MYERROR <> 0
BEGIN
PRINT 'INSERT ERROR ' + CONVERT(VARCHAR, @.@.ERROR)
ROLLBACK TRANSACTION
RETURN
END
DELETE FROM dbo.sequencetooling
WHERE DATEDIFF(d, deliverydate, GETDATE()) >= 14
SET @.MYERROR = @.@.ERROR
IF @.MYERROR <> 0
BEGIN
PRINT 'DELETE ERROR ' + CONVERT(VARCHAR, @.@.ERROR)
ROLLBACK TRANSACTION
RETURN
END
COMMIT TRANSACTION
Jo SegersHi
Relying on default error handling to be the same in future versions of SQL
Server is not good at all as it could change.
You should always handle the error yourself (like in your code) as the
execution will be predictable.
Regards
Mike
"Jo Segers" wrote:

> Hi,
> I have a job that executes the TSQL below. Do I need to do the
> errorchecking myself (as below) or does the transaction automaticaly
> rollback if a statement fails?
> Feel free to comment on the script. (Its never too late to learn).
> Yours sincerely,
> Jo
> SET NOCOUNT ON
> SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
> DECLARE @.MYERROR
> BEGIN TRANSACTION
> INSERT INTO sequencetoolingbak
> (
> orderid,
> partno,
> jobno,
> deliverydate,
> time_of_creation
> )
> SELECT orderid, partno, jobno, deliverydate, time_of_creation
> FROM dbo.sequencetooling AS seq
> WHERE deliverydate < DATEADD(d, -14, GETDATE())
> AND NOT EXISTS
> (
> SELECT orderid
> FROM dbo.sequencetoolingbak AS bak
> WHERE bak.orderid = seq.orderid
> )
> SET @.MYERROR = @.@.ERROR
> IF @.MYERROR <> 0
> BEGIN
> PRINT 'INSERT ERROR ' + CONVERT(VARCHAR, @.@.ERROR)
> ROLLBACK TRANSACTION
> RETURN
> END
> DELETE FROM dbo.sequencetooling
> WHERE DATEDIFF(d, deliverydate, GETDATE()) >= 14
>
> SET @.MYERROR = @.@.ERROR
> IF @.MYERROR <> 0
> BEGIN
> PRINT 'DELETE ERROR ' + CONVERT(VARCHAR, @.@.ERROR)
> ROLLBACK TRANSACTION
> RETURN
> END
> COMMIT TRANSACTION
> --
> Jo Segers
>|||The transaction will not be automatically rolled back, unless you SET
XACT_ABORT ON first. Some errors will terminate the batch, which means that
your error handler won't be executed though.
For a complete explanation of errorhandling in SQL Server, see the articles
by SQL Server MVP Erland Sommarskog on www.sommarskog.se .
Jacco Schalkwijk
SQL Server MVP
"Jo Segers" <jo.segers@.alro.be> wrote in message
news:eSgVzgsAFHA.4008@.TK2MSFTNGP09.phx.gbl...
> Hi,
> I have a job that executes the TSQL below. Do I need to do the
> errorchecking myself (as below) or does the transaction automaticaly
> rollback if a statement fails?
> Feel free to comment on the script. (Its never too late to learn).
> Yours sincerely,
> Jo
> SET NOCOUNT ON
> SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
> DECLARE @.MYERROR
> BEGIN TRANSACTION
> INSERT INTO sequencetoolingbak
> (
> orderid,
> partno,
> jobno,
> deliverydate,
> time_of_creation
> )
> SELECT orderid, partno, jobno, deliverydate, time_of_creation
> FROM dbo.sequencetooling AS seq
> WHERE deliverydate < DATEADD(d, -14, GETDATE())
> AND NOT EXISTS
> (
> SELECT orderid
> FROM dbo.sequencetoolingbak AS bak
> WHERE bak.orderid = seq.orderid
> )
> SET @.MYERROR = @.@.ERROR
> IF @.MYERROR <> 0
> BEGIN
> PRINT 'INSERT ERROR ' + CONVERT(VARCHAR, @.@.ERROR)
> ROLLBACK TRANSACTION
> RETURN
> END
> DELETE FROM dbo.sequencetooling
> WHERE DATEDIFF(d, deliverydate, GETDATE()) >= 14
>
> SET @.MYERROR = @.@.ERROR
> IF @.MYERROR <> 0
> BEGIN
> PRINT 'DELETE ERROR ' + CONVERT(VARCHAR, @.@.ERROR)
> ROLLBACK TRANSACTION
> RETURN
> END
> COMMIT TRANSACTION
> --
> Jo Segers|||Mike Epprecht (SQL MVP) wrote:
> Hi
> Relying on default error handling to be the same in future versions of SQL
> Server is not good at all as it could change.
> You should always handle the error yourself (like in your code) as the
> execution will be predictable.
> Regards
> Mike
OK,
Thanks for your reply.
Jo Segers