"WHEN OTHERS THEN NULL" in SQL Server 2005

Question

tracking_table is a log table declared as follows:

create table tracking_table (my_command nvarchar(500), my_date datetime);

Please suppose you have the following block of SQL SERVER 2005 code, declared within a SQL Server 2005 job:

DECLARE @my_statement NVARCHAR(500)  
delete from tracking_table
   SET @my_statement = 'ALTER INDEX ALL ON my_user.dbo.my_fact_table REBUILD WITH (FILLFACTOR = 90)'
insert into tracking_table values (@my_statement,getdate()) 
EXEC (@my_statement)  
 SET @my_statement = 'ALTER INDEX ALL ON my_user.dbo.my_second_table REBUILD WITH (FILLFACTOR = 90)'
insert into tracking_table (@my_statement,getdate())    
EXEC (@my_statement)

At runtime, if the first statement (ALTER INDEX ALL ON my_user.dbo.my_fact_table REBUILD WITH (FILLFACTOR=90)) fails, the second statement which acts on my_second table WON'T be executed.

I would like to know how could I modify the SQL Server 2005 code, in order to skip any error, going forward (in Oracle I would say, WHEN OTHERS THEN NULL).

How could I achieve this?

Thank you in advance for your kind help.

You could put each statement into its own job step, and then define what to do for each step if a failure occurs (stop execution, notify via e-mail, continue on, etc.) — marc_s, Oct 31 '12 at 06:25
I am obliged to use only one job step with all the code inside. — UltraCommit, Oct 31 '12 at 06:26

score 2 · Accepted Answer · edited May 23 '17 at 11:55

I cannot advice to suppress errors, but if you really want to do it, I think you can try:

declare @my_statement nvarchar(500)  

begin try
    delete from tracking_table
end try
begin catch
    print null // or errormessage
end catch

begin try
    set @my_statement = 'ALTER INDEX ALL ON my_user.dbo.my_fact_table REBUILD WITH (FILLFACTOR = 90)'

    insert into tracking_table values (@my_statement,getdate()) 
    exec (@my_statement)  
end try
begin catch
    print null // or errormessage
end catch

begin try
    set @my_statement = 'ALTER INDEX ALL ON my_user.dbo.my_second_table REBUILD WITH (FILLFACTOR = 90)'

    insert into tracking_table (@my_statement,getdate())    
    exec (@my_statement)  
end try
begin catch
    print null // or errormessage
end catch

you can also create procedure

create procedure sp_executesql_Suppress_Errors
(
     @stmt nvarchar(max)
)
as
begin
    begin try
        exec sp_executesql
            @stmt = @stmt
    end try
    begin catch
        print null // or errormessage
    end catch
end

and then call it with your statements. I also advice you to use exec sp_executesql instead of exec (see Dynamic SQL - EXEC(@SQL) versus EXEC SP_EXECUTESQL(@SQL))

Thank you very much for your kind cooperation. Answer accepted! — UltraCommit, Oct 31 '12 at 06:28
Thank you again for your sp_Suppress_Errors procedure! Useful!! — UltraCommit, Oct 31 '12 at 06:32

"WHEN OTHERS THEN NULL" in SQL Server 2005

1 Answers1