Transaction question

[MAB71]

I wrote a procedure in SQL 6.5 which is something like

CREATE PROCEDURE abc

AS

BEGIN TRAN xyz

insert statement ..

insert statement ...

update statement...

insert statement ...

insert statement ...
COMMIT TRAN xyz


I want either all statements to execute or none.

My question is, if an error occurs like a constraint is violated, where will
the control pass? to the next statement or will it exit the procedure? and
do I need to explicitly issue a rollback tran? and if yes then where? and if
no then will the server itself rollback the changes made prior to the error?

thx

[Ron Talmage]

MAB71,

You have to trap for non-fatal errors in your code by capturing the @@ERROR
value. If there is an error, then roll back the transaction and exit from
the stored procedure.

Fatal errors will automatically roll back the transaction and abort the
batch.

Ron
--
Ron Talmage
SQL Server MVP
"MAB71" <b1*********@yahoo.com> wrote in message
news:bo*************@ID-31123.news.uni-berlin.de...

I wrote a procedure in SQL 6.5 which is something like

CREATE PROCEDURE abc

AS

BEGIN TRAN xyz

insert statement ..

insert statement ...

update statement...

insert statement ...

insert statement ...
COMMIT TRAN xyz


I want either all statements to execute or none.

My question is, if an error occurs like a constraint is violated, where will the control pass? to the next statement or will it exit the procedure? and
do I need to explicitly issue a rollback tran? and if yes then where? and if no then will the server itself rollback the changes made prior to the error?
thx