Auto Rollback of transaction

Hello,
I had a discuss of SQL Server's auto rollback behaviour with my colleague.
He hasn't convinced me.
We use SQL Server 2000.
What I've noticed is that, inside a stored procedure, after BEGIN TRANS
txTransName
If an error is generated while manipulating table data, then the whole
transaction will roll back by SQL server, and SQL does not process any other
code further down the SP.
However if an error is generated by 'non-table minipulation statement', for
example, do a SELECT 100/0 (division by zero), then SQL does not roll back
the transaction.
The point of this discussion is that, for a while, we've been checking for
errors (IF @.@.ERROR <> 0....do something) right after table minipulation
statements. Now I see that this way of checking for error is actually
pointless, since SQL jumps right out of the SP when it encounters table
minipulation errors anyway.
But I can't be 100% sure about my argument. So is there anyone out there
that can confirm what I am thinking is right or wrong?
Thank you for your time.
ConaxHI
Read this
http://www.sommarskog.se/error-handling-II.html
Regards
R.D
"Conax" wrote:

> Hello,
> I had a discuss of SQL Server's auto rollback behaviour with my colleague.
> He hasn't convinced me.
> We use SQL Server 2000.
> What I've noticed is that, inside a stored procedure, after BEGIN TRANS
> txTransName
> If an error is generated while manipulating table data, then the whole
> transaction will roll back by SQL server, and SQL does not process any oth
er
> code further down the SP.
> However if an error is generated by 'non-table minipulation statement', fo
r
> example, do a SELECT 100/0 (division by zero), then SQL does not roll back
> the transaction.
> The point of this discussion is that, for a while, we've been checking for
> errors (IF @.@.ERROR <> 0....do something) right after table minipulation
> statements. Now I see that this way of checking for error is actually
> pointless, since SQL jumps right out of the SP when it encounters table
> minipulation errors anyway.
> But I can't be 100% sure about my argument. So is there anyone out there
> that can confirm what I am thinking is right or wrong?
> Thank you for your time.
> Conax
>
>|||Hi Conax,
As you can see from the following sample code in both cases 1) table
manipulation
and 2) non table manipluation, the stored proc does continue and @.@.error
should be checked right after the statement in question.
Please check Books online (BOL) for more information on @.@.error checking.
When in doubt, its a good idea to type a sample test code and see how things
work.
HTH...
set nocount on
go
create table t
(
c1 int not null,
c2 int
)
go
insert t values(1,1)
insert t values(1,2)
insert t values(2,1)
insert t values(2,1)
go
CREATE PROCEDURE dbo.proctblerror
AS
/* you mentioned...
What I've noticed is that, inside a stored procedure, after BEGIN TRANS
txTransName
If an error is generated while manipulating table data, then the whole
transaction will roll back by SQL server, and SQL does not process any other
code further down the SP.
*/
begin tran txTransName
update t set
c1 = null
where c2=1
if (@.@.error = 0)
begin commit tran txTransName print 'commited tbl manip' end
else
begin rollback tran txTransName print 'rolled back tbl manip' end
print 'running rest of tbl manip proc'
select * from t
GO
EXECUTE dbo.proctblerror
GO
CREATE PROCEDURE dbo.procnontblerror
AS
/*
You also mentioned...
However if an error is generated by 'non-table minipulation statement', for
example, do a SELECT 100/0 (division by zero), then SQL does not roll back
the transaction.
*/
begin tran txTransName
select 222
if (@.@.error = 0)
begin commit tran txTransName print 'commited non tbl manip' end
else
begin rollback tran txTransName print 'rolled back non tbl manip' end
print 'running rest of non tbl manip proc'
select * from t
GO
EXECUTE dbo.procnontblerror
GO
drop table t
DROP PROCEDURE dbo.proctblerror
drop proc dbo.procnontblerror
GO
http://zulfiqar.typepad.com
BSEE, MCP
"Conax" wrote:

> Hello,
> I had a discuss of SQL Server's auto rollback behaviour with my colleague.
> He hasn't convinced me.
> We use SQL Server 2000.
> What I've noticed is that, inside a stored procedure, after BEGIN TRANS
> txTransName
> If an error is generated while manipulating table data, then the whole
> transaction will roll back by SQL server, and SQL does not process any oth
er
> code further down the SP.
> However if an error is generated by 'non-table minipulation statement', fo
r
> example, do a SELECT 100/0 (division by zero), then SQL does not roll back
> the transaction.
> The point of this discussion is that, for a while, we've been checking for
> errors (IF @.@.ERROR <> 0....do something) right after table minipulation
> statements. Now I see that this way of checking for error is actually
> pointless, since SQL jumps right out of the SP when it encounters table
> minipulation errors anyway.
> But I can't be 100% sure about my argument. So is there anyone out there
> that can confirm what I am thinking is right or wrong?
> Thank you for your time.
> Conax
>
>|||Thanks R.D and Z.S, you've been a great help.
Regards
Conax
"Conax" <ConaxLiu@.hotmail.com> wrote in message
news:e%23hlvbCtFHA.1472@.TK2MSFTNGP15.phx.gbl...
> Hello,
> I had a discuss of SQL Server's auto rollback behaviour with my colleague.
> He hasn't convinced me.
> We use SQL Server 2000.
> What I've noticed is that, inside a stored procedure, after BEGIN TRANS
> txTransName
> If an error is generated while manipulating table data, then the whole
> transaction will roll back by SQL server, and SQL does not process any
other
> code further down the SP.
> However if an error is generated by 'non-table minipulation statement',
for
> example, do a SELECT 100/0 (division by zero), then SQL does not roll back
> the transaction.
> The point of this discussion is that, for a while, we've been checking for
> errors (IF @.@.ERROR <> 0....do something) right after table minipulation
> statements. Now I see that this way of checking for error is actually
> pointless, since SQL jumps right out of the SP when it encounters table
> minipulation errors anyway.
> But I can't be 100% sure about my argument. So is there anyone out there
> that can confirm what I am thinking is right or wrong?
> Thank you for your time.
> Conax
>