Useally I am catching error like this
EXECUTE (@STATEMENT)
SELECT @ERR_CODE = @@ERROR
its working on simple errors , like below query .. @@ERROR return value
insert into tab1 values(1) -- error attempt to insert unique constraint
however the below query also give unique constraint error however the @ERROR dont catch it it return null value
insert into tab1 select id from tab2
so the above statement give unique constraint but @ERROR doesnt catch it
another example I have the below error
sybase could not acquire a lock within the specified wait period
@ERROR didnt catch either
My question is there a way where I can catch any error where executing statement ?
Are you absolutely sure you've got no statement between your insert with a constraint failure and looking at @@error? Anything at all would reset @@error.
It's not the fact that you are using dynamic SQL in EXECUTE() - @@error will still be available.
Try doing the same as me below - do you get different?
create table tempdb..abe(a int)
select 1 a into #a
insert #a values (1)
create unique index x on tempdb..abe(a)
insert tempdb..abe select * from #a
Msg 2601, Level 14, State 2:
Server 'CRENG_QA', Line 1:
Attempt to insert duplicate key row in object 'abe' with unique index 'x'
Command has been aborted.
(0 rows affected)
select @@error
-----------
2601
execute('insert tempdb..abe select * from #a')
Msg 2601, Level 14, State 2:
Server 'CRENG_QA', Line 1:
Attempt to insert duplicate key row in object 'abe' with unique index 'x'
Command has been aborted.
(0 rows affected)
select @@error
-----------
2601
