Hi masters,
i have learned in this site that if i want to protect my stored procedures, from executing some statements even if other fails (inside the stored proc) that i can do like below:
create proc test as declare @ierror int
begin transaction
update table1 set name ='paul' set @ierror =@@error
if @ierror = 0 begin update table2 set address ='xpto' set @ierror =@@error end
if @ierror = 0 begin update table3 set address2 ='xpto2' set @ierror =@@error end
if @ierror=0 begin commit transaction end
GO
If i am right, the stored procedure will only execute the updates, if non of them(updates) fail.
I will not use the "xact_abort on", because i think that it is no need in this case.
I don't need that the stored procedure stops if it raises any error. i only need that it can not execute any statement, if one of the statements fails.
Is this correct?
If the anwser is yes, then i would like to make other questions about errors in SQL Server 2000. I know that the errors that are raised, have severity levels.
But i don't know if what i will write below is correct:
1 )taking the stored proc test on this post as an e.g, if one of the instructions raises an error with a severity level of 15 or below, then the stored procedure will not stop from executing, it will continue to execute until the last statement, but it will not commit anything to the database because one of the instructions have failed with that severity level.
2) If one of the instructions raises an error of severity level of 16 or bigger, then the stored procedure will not go to the other instructions. It will stop executing.
is this correct?
tks,
Pedro
|