Ignoring error and continue

  • Hi,

    I am writing a Stored Procedure to update a table in multiple databases. In some on the databases that table doesnot exist. So, I want to ignore the error and continue to next database update.

    I also want to stop showing error at all while executing the Stored Procedure.

    So, please help to ignore and disable the showing of errors.

    Thanks in advance.

    Afreen

  • Rather than ignoring the error, it would be better to avoid it by first checking for the table's existence:

    IF OBJECT_ID('dbo.Test', 'U') IS NOT NULL
    BEGIN
    -- Add code here
    END;

    If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.

  • Hi Phir,

    Thanks for the response. I will try your suggestion.

    But, also want to check if there is a way to ignore the errors and stop showing error message?

    I have to implement more functionality where I guess I will need to ignore the errors.

     

    Thanks.

  • I don't approve of doing this – it wouldn't get past me in a code review – but you can do something like this:

    BEGIN TRY
    SELECT 1 / 0;
    END TRY
    BEGIN CATCH
    END CATCH;

    SELECT 'Error got swallowed'

    If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.

  • Hi Phil,

    Thanks. But, it is still showing errors.

     

  • Hi Phil,

    Below is my code: @db_name is coming from the cursor which is fetching the db_name from master database.

    SET @qry = 'SET NOCOUNT ON Declare @days INT BEGIN TRY select @days=LongValue from ' + @db_name + '.dbo.Settings

    where ID = ''100''

    PRINT @days

    update ' + @db_name + '.dbo.employee

    set isactive = 0

    where abs(datediff(day, GETDATE(),LoginDate)) > @days

    PRINT @@ROWCOUNT

    END TRY

    BEGIN CATCH

    END CATCH;'

    EXEC(@qry)

    It is executing update statement fine for the dbs where employee table is present, but where the employee table is not there it is giving error.

    I want ignore the error and dont want to show them at all.

    Please help me on this.

     

  • Phil Parkin wrote:

    I don't approve of doing this – it wouldn't get past me in a code review – but you can do something like this:

    BEGIN TRY
    SELECT 1 / 0;
    END TRY
    BEGIN CATCH
    END CATCH;

    SELECT 'Error got swallowed'

    I think if a query referenced a non existing table, errors work differently, this snippet didn't print or select anything! Sort of like a "compile time" error.

    BEGIN TRY
    select bugs from bogus_table_name
    END TRY
    BEGIN CATCH
    print 'caught error'
    END CATCH;
    SELECT 'Error got swallowed'

    edit: this is like the worst forum software ever!

    • This reply was modified 3 years, 9 months ago by  x.
  • How about this?

    SET @qry
    = 'SET NOCOUNT ON Declare @days INT select @days=LongValue from ' + @db_name
    + '.dbo.Settings

    where ID = ''100''

    PRINT @days

    update ' + @db_name
    + '.dbo.employee

    set isactive = 0

    where abs(datediff(day, GETDATE(),LoginDate)) > @days

    PRINT @@ROWCOUNT

    ';

    BEGIN TRY
    EXEC (@qry);
    END TRY
    BEGIN CATCH
    END CATCH;

    If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.

  • The problem with this is, it comes out of the cursor and do not continue Fetch statement.

     

     

  • And the problem with your question is that you did not give me the full picture.

    Now you are drip-feeding clues into the thread as they come up, which is not my preferred way of working. Maybe someone else, who has more patience than I, will assist.

    If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.

  • Phil Parkin wrote:

    And the problem with your question is that you did not give me the full picture.

    Now you are drip-feeding clues into the thread as they come up, which is not my preferred way of working. Maybe someone else, who has more patience than I, will assist.

    Nah... it was just easy to miss.  See this post of the op.

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • To get SQL Server to ignore and continue is possible from a script or procedure by executing another (or more...) stored procedure which contain try/catch where the error is output as a variable.   Within the scope of a single statement "ignoring an exception" is nonsensical.  From PROC_A run PROC_B and output result from PROC_B TRY/CATCH (depending on what happened).

    Aus dem Paradies, das Cantor uns geschaffen, soll uns niemand vertreiben können

  • Hi Phil,

    I am sorry if my question/code created a confusion. I am putting the entire code below. Could you please look at that:

    ***********************************************************************

    DECLARE @db_name VARCHAR(100), @qry VARCHAR(500)

    Declare @msg varchar(100)

    DECLARE db_cursor CURSOR FOR
    SELECT Name FROM Alldatabases
    ORDER BY Name

    OPEN wh_cursor

    FETCH NEXT FROM db_cursor
    INTO @db_name

    WHILE @@FETCH_STATUS = 0
    BEGIN

    SET @db_name = @db_name

    SET @qry
    = 'SET NOCOUNT ON Declare @days INT select @days=LongValue from ' + @db_name + '.dbo.Settings
    where ID = ''100''
    PRINT @days
    update ' + @db_name + '.dbo.employee
    set isactive = 0
    where abs(datediff(day, GETDATE(),LoginDate)) > @days

    PRINT @@ROWCOUNT'

    EXEC(@qry)

    SET @msg = convert(varchar(100), @@ROWCOUNT) + ' rows updated.'

    FETCH NEXT FROM db_cursor
    INTO @db_name

    END

    CLOSE db_cursor
    DEALLOCATE db_cursor?

     

    ***************************************************************

    I appreciate your time and sorry again.

  • You cannot get around this. When your batch is compiled, the UPDATE statement checks for object existence. This will cause an error. Not even in a TRY..CATCH

    2020-06-18 08_00_09-SQLQuery2.sql - ARISTOTLE_SQL2017.Sandbox (ARISTOTLE_Steve (55))_ - Microsoft SQ

    The only way to do this is perform the check outside the UPDATE statement string concatenation.

    IF EXISTS (SELECT * FROM sys.objects WHERE name = 'employee')
    BEGIN

    SET @qry = 'SET NOCOUNT ON Declare @days INT

    BEGIN TRY

    update ' + @db_name + '.dbo.employee
    set isactive = 0
    where employeeid = 1
    end try
    begin catch
    select ''no table in ' + @db_name + ' ''
    end catch
    PRINT @@ROWCOUNT
    '
    EXEC(@qry)
    END

  • There's a lot going on in the code which could be looked at.  Instead of EXEC(@qry) where the parameters are strung together it could be parameterized using sp_executesql.  In terms of ignore/continue if the object does not exist what Steve says above is exactly right.  Although stored procedures are compiled with late binding to objects, meaning you can create a procedure which references a table that doesn't exist.  But when the procedure is run if the table doesn't exist nothing will catch the error.

    Suppose there are: 1 table (dbo.test_t) and 2 procedures (dbo.test_proc_a and dbo.test_proc_b).

    drop table if exists dbo.test_t;
    go
    create table dbo.test_t(x int not null);
    go
    drop proc if exists dbo.test_proc_a;
    go
    create proc dbo.test_proc_a
    as
    set nocount on;
    set xact_abort on;

    begin transaction
    begin try
    declare
    @output_bit bit,
    @output_msg nvarchar(max);

    exec dbo.test_proc_b N'dbo.test_t', @result_bit=@output_bit output, @result_msg=@output_msg output;
    print (cast(@output_bit as nchar(1)));
    print (cast(@output_msg as nvarchar(max)));
    print ('xact_state='+cast(xact_state() as nvarchar(3)));

    if (xact_state()=-1)
    begin
    rollback transaction;
    begin transaction;
    end

    exec dbo.test_proc_b N'pdq', @result_bit=@output_bit output, @result_msg=@output_msg output;
    print (cast(@output_bit as nchar(1)));
    print (cast(@output_msg as nvarchar(max)));
    print ('xact_state='+cast(xact_state() as nvarchar(3)));

    if (xact_state()=-1)
    begin
    rollback transaction;
    begin transaction;
    end

    exec dbo.test_proc_b N'dbo.test_t', @result_bit=@output_bit output, @result_msg=@output_msg output;
    print (cast(@output_bit as nchar(1)));
    print (cast(@output_msg as nvarchar(max)));
    print ('xact_state='+cast(xact_state() as nvarchar(3)));

    if (xact_state()=-1)
    begin
    rollback transaction;
    end

    if (@@trancount>0)
    commit transaction;
    end try
    begin catch
    print (error_message());

    rollback transaction;
    end catch
    go
    drop proc if exists dbo.test_proc_b;
    go
    create proc dbo.test_proc_b
    @sys_table_name nvarchar(256),
    @result_bit bit output,
    @result_msg nvarchar(max) output
    as
    set nocount on;
    begin try
    /* test to make sure the table exists */
    --if (object_id(@sys_table_name, 'U') is null)
    -- throw 50000, 'The table does not exist', 1;
    if (object_id('dbo.test_t', 'U') is null)
    throw 50000, 'The table does not exist', 1;

    /* 1) do something that works */
    insert dbo.test_t(x) values(1);

    /* 2) create an exception on purpose divide by zero and return system error message*/
    --insert dbo.test_t(x) values(4/0);

    /* 3) create an exception on purpose using THROW */
    --declare @error_msg nvarchar(max)=concat(N'test_proc_b input @sys_table_name=', @sys_table_name);
    --throw 50000, @error_msg, 1;

    select @result_bit=cast(1 as bit);
    select @result_msg=N'Ok';
    end try
    begin catch
    select @result_bit=cast(0 as bit);
    select @result_msg=error_message();
    end catch
    go

    test_proc_a executes test_proc_b three times passing in different table names and outputting variables to ignore/continue if dbo.test_proc_b was successful or not.

    Test 1) Suppose the three objects above are created: drop table dbo.test_t and execute dbo.test_proc_a

    drop table if exists dbo.test_t;
    go
    exec dbo.test_proc_a;

    Results

    0
    The table does not exist
    0
    The table does not exist
    0
    The table does not exist

    Test 2) Suppose the three objects above are created (don't drop table)

    exec dbo.test_proc_a;

    Results

    1
    Ok
    1
    Ok
    1
    Ok

    Test 3) Suppose the three objects above are created.  Uncomment #2 in dbo.test_proc_b and comment out #1

    drop proc if exists dbo.test_proc_b;
    go
    create proc dbo.test_proc_b
    @sys_table_name nvarchar(256),
    @result_bit bit output,
    @result_msg nvarchar(max) output
    as
    set nocount on;
    begin try
    /* test to make sure the table exists */
    --if (object_id(@sys_table_name, 'U') is null)
    -- throw 50000, 'The table does not exist', 1;
    if (object_id('dbo.test_t', 'U') is null)
    throw 50000, 'The table does not exist', 1;

    /* 1) do something that works */
    --insert dbo.test_t(x) values(1);

    /* 2) create an exception on purpose divide by zero and return system error message*/
    insert dbo.test_t(x) values(4/0);

    /* 3) create an exception on purpose using THROW */
    --declare @error_msg nvarchar(max)=concat(N'test_proc_b input @sys_table_name=', @sys_table_name);
    --throw 50000, @error_msg, 1;

    select @result_bit=cast(1 as bit);
    select @result_msg=N'Ok';
    end try
    begin catch
    select @result_bit=cast(0 as bit);
    select @result_msg=error_message();
    end catch
    go

    exec dbo.test_proc_a;

    Results

    0
    Divide by zero error encountered.
    0
    Divide by zero error encountered.
    0
    Divide by zero error encountered.

    Test 4) Suppose the three objects above are created.  Uncomment #3 in dbo.test_proc_b and comment out #2

    drop proc if exists dbo.test_proc_b;
    go
    create proc dbo.test_proc_b
    @sys_table_name nvarchar(256),
    @result_bit bit output,
    @result_msg nvarchar(max) output
    as
    set nocount on;
    begin try
    /* test to make sure the table exists */
    --if (object_id(@sys_table_name, 'U') is null)
    -- throw 50000, 'The table does not exist', 1;
    if (object_id('dbo.test_t', 'U') is null)
    throw 50000, 'The table does not exist', 1;

    /* 1) do something that works */
    --insert dbo.test_t(x) values(1);

    /* 2) create an exception on purpose divide by zero and return system error message*/
    --insert dbo.test_t(x) values(4/0);

    /* 3) create an exception on purpose using THROW */
    declare @error_msg nvarchar(max)=concat(N'test_proc_b input @sys_table_name=', @sys_table_name);
    throw 50000, @error_msg, 1;

    select @result_bit=cast(1 as bit);
    select @result_msg=N'Ok';
    end try
    begin catch
    select @result_bit=cast(0 as bit);
    select @result_msg=error_message();
    end catch
    go

    exec dbo.test_proc_a;

    Results

    0
    test_proc_b input @sys_table_name=dbo.test_t
    0
    test_proc_b input @sys_table_name=pdq
    0
    test_proc_b input @sys_table_name=dbo.test_t

     

    • This reply was modified 3 years, 9 months ago by  Steve Collins.

    Aus dem Paradies, das Cantor uns geschaffen, soll uns niemand vertreiben können

Viewing 15 posts - 1 through 15 (of 25 total)

You must be logged in to reply to this topic. Login to reply