April 22, 2009 at 4:04 pm
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
April 22, 2009 at 7:32 pm
If one of the statements fails your procedure leave the transaction open.
Completing of procedure code does not complete transaction.
it should be like this:
if @ierror=0
begin
commit transaction
end
ELSE
begin
rollback transaction
end
_____________
Code for TallyGenerator
April 23, 2009 at 10:02 am
It's done, tks.
What about the:
"Set nocount on" does this instructuction affects the error count (@@error) inside the stored procedure while it is executing?
Or the Set nocount on, only affects the lines returned by the stored procedure?
tks
April 23, 2009 at 10:23 am
pedro.ribeiro (4/23/2009)
It's What about the:"Set nocount on" does this instructuction affects the error count (@@error) inside the stored procedure while it is executing?
Or the Set nocount on, only affects the lines returned by the stored procedure?
tks
see http://msdn.microsoft.com/en-us/library/ms189837.aspx
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
April 24, 2009 at 7:23 am
I have seen your link. thank you
I have seen taht the variable @@error is not affected by the "Set nocount on".
But, what about the:
@declare erro int
set @erro =@@erro
The variable that i declared (@erro) is not affected by the "Set nocount on"?
Other question that i have is this:
if @erro=0
begin
update bulk_contribuinteinst set valido_bulk=1,motivo_irregular_bulk =null
where nif_antigo in
(select rgc1 from CTBS_BULK_VALIDOS_REPETIDOS
where motivo_irregular_bulk1 ='NIF REPETIDO' and rgc1 not in
(select a.rgc1 as rgc from CTBS_BULK_VALIDOS_REPETIDOS as a inner join allctb as b
on a.rgc1 isnull(b.rgc,-111) and a.nifctb1 =b.nifctb))
set @erro=@@error
end
the update is the first line of the instruction , then i have a select.
I am putting the "Set @erro=@@error", on the end of the statement, to verify it it went ok (if succeded). Is this correct?, or should the "set @erro=@@error"
be afetr the update and befour the select?
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply