Try Catch Scope

i have sp purges data multiple databases.

 

i’m running sp database named db100

 

i have try\catch goes was:

 

-------------------------------------------

begin try

 

            delete

      from  [db101].[dbo].[atable]

      where xyz

 

delete

      from  [db102].[dbo].[btable]

      where xyz

            …

 

end try

 

begin catch

insert into [db100].[dbo].[log_table]

(stepnumber, description, desctype)

values( 2, error_message(), 'error' )

end catch

 

when try run code try not able catch error happened on db101 , db102 while code running on db100.

any on how may catch error?

 

thanks

adam,

could modify erland's script can reproduce problem?

the try / catch construct catches execution errors have severity higher 10 not close database connection (this bol), , not compilation ones. sql server allow create stored procedures referencing non existing tables because check existence of them during compilation time (before executing). during creation of sp statements checked sure syntactically correct, postpone check non existing tables, which known deferred name resolution. if reference existing table, sql server check column being referenced table exists.

examples:

-- script not catch error during compilation time. 
begin try
    delete table_does_not_exist;
end try
begin catch
    print 'error';
end catch;
go
create procedure p1
as
select c1, c2, c3
t
go
-- if try execute sp, error
exec p1;
go
drop procedure p1;
go

 

---

amb

some guidelines posting questions...

SQL Server  >  Transact-SQL