The catch does NOT catch

Question

begin try 

    delete from NonexistingTable

end try 

begin catch 

    print 'catch u' 

end catch

 in the above code where NonexistingTable is a non-existing table, the catch block does NOT catch the error. Why? thanks. Andrew

---

Interested in BI related technologies

Answer 1

begin try delete from NonexistingTable end try begin catch print 'catch u' end catch in the above code where NonexistingTable is a non-existing table, the catch block does NOT catch the error. Why? thanks. Andrew

---

Interested in BI related technologies

Answer 2

 

begin try

 

      delete from foo

end try

begin catch

      print 'catch u'

end catch

 

it gives me invalid object name 'foo' syntax error

 

Try...CATCH doesn't handle errors with severity 10 and under

 

 

BEGIN TRY
    -- Raise an error of severity 10. This will not invoke the
    -- CATCH block and the message is returned to the client.
    RAISERROR ('Informational Message', 10, 1);

    -- Generate divide-by-zero error. This will invoke the
    -- CATCH block
    SELECT 1/0;
END TRY
BEGIN CATCH
    SELECT 
        ERROR_NUMBER() AS ErrorNumber,
        ERROR_SEVERITY() AS ErrorSeverity,
        ERROR_STATE() as ErrorState,
        ERROR_LINE() as ErrorLine,
        ERROR_MESSAGE() as ErrorMessage;
END CATCH;
GO

Answer 3

Chirag is correct, these errors are not caught in the CATCH block, you might want to check if the table exists first, and if it doesn't you can use GOTO and create a block to send you a message or perform another operation

---

Abdallah, PMP, ITIL, MCTS

Answer 4

forum members continue here

http://social.msdn.microsoft.com/Forums/en-US/transactsql/thread/0450b4d6-05e4-4166-b1fa-c183f51684e7

Answer 5

This is because of deferred name resolution in SQL Server. You can read in more details here:
http://www.sommarskog.se/strict_checks.html#killDNR

One way to catch such errors is in outer procedure that has TRY...CATCH. Using TRY...CATCH at the same level will not work.

---

Plamen Ratchev

Answer 6

Chirag is correct, these errors are not caught in the CATCH block, you might want to check if the table exists first, and if it doesn't you can use GOTO and create a block to send you a message or perform another operation

---

Abdallah, PMP, ITIL, MCTS

it seems the try-catch does NOT behave the same way as other programming languages(eg Java),where the catch block can catch all the error?
What to do if I want to catch all the error?

thanks.


Andrew

---

Interested in BI related technologies

Answer 7

This is because of deferred name resolution in SQL Server. You can read in more details here:
http://www.sommarskog.se/strict_checks.html#killDNR

One way to catch such errors is in outer procedure that has TRY...CATCH. Using TRY...CATCH at the same level will not work.

---

Plamen Ratchev

Thanks, Plamen
outer procedure still can NOT catch  errors raised in inner procedure with severity 10 and under

---

Interested in BI related technologies

Answer 8

outer procedure still can NOT catch 

---

Interested in BI related technologies

Andrew,

You are right. Plain nesting of TRY-CATCH will not do it.

I know of two ways to nest it: stored procedure nesting and dynamic SQL as shown following:

-- TRY-CATCH nesting demo
BEGIN TRY -- OUTER TRY-CATCH
EXEC ('begin try 
    delete from NonexistingTable
end try 
begin catch 
    print ''catch u'' 
end catch')
END TRY
BEGIN CATCH
    print 'catch u in OUTER TRY-CATCH' 
END CATCH
-- catch u in OUTER TRY-CATCH

Related article: http://www.sqlusa.com/articles2008/trycatch/

---

Kalman Toth, SQL Server & BI Training, SSAS, SSIS, SSRS; http://www.SQLUSA.com

Answer 9

Here is one way to check if the table exists. If it does do some action, else daise an error

    IF EXISTS (SELECT 1 FROM sys.objects WHERE Object_id = object_id(N'SomeTable'))
    DELETE FROM SomeTable
    ELSE
    RAISERROR ('Table doesn''t exist', 10, 1);

---

Abdallah, PMP, ITIL, MCTS

Answer 10

Non existing object raises error of severity 16 and you can catch it in the outer procedure. Here is a proof:

BEGIN TRY
EXEC ('
BEGIN TRY
    DELETE FROM NonexistingTable;
END TRY 
BEGIN CATCH
    PRINT ERROR_MESSAGE(); 
END CATCH');
END TRY
BEGIN CATCH
    PRINT ERROR_MESSAGE();
END CATCH


/*

Invalid object name 'NonexistingTable'.

*/

---

Plamen Ratchev

Answer 11

Andrew,

You are right. Plain nesting of TRY-CATCH will not do it.

I know of two ways to nest it: stored procedure nesting and dynamic SQL as shown following:

---

Kalman Toth, SQL Server & BI Training, SSAS, SSIS, SSRS; http://www.SQLUSA.com

Hi,Kalman
thanks,
and would u please show me an example of stored procedure nesting(which is not plain)

Regards,
Andrew

Answer 12

Keep in mind how this is actually working.

When the stored proc sprocDelta is called, it then calls sprocAlpha, which simply TERMINATES at the "DELETE...." line, no other processing in sprocAlpha continues. The error is then caught by the outer stored proc sprocDelta.

Answer 13

Hi,all

Neither dynamic SQL nor procedure nesting can catch error with severity 10 and under.

---

Interested in BI related technologies

Answer 14

Andrew, I think you should check if the object exists before you do any action just like in the example I gave you.

---

Abdallah, PMP, ITIL, MCTS

Answer 15

Andrew, I think you should check if the object exists before you do any action just like in the example I gave you.

---

Abdallah, PMP, ITIL, MCTS

Thanks,Abdallah
I do appreciate ur suggestion.

while,I am more interested in the principle of try-catch block.


Andrew

---

Interested in BI related technologies

Answer 16

Yes, and there is a reason for that. Severity 10 and under is for informational messages and warnings which are not considered errors and as such should not interrupt the execution of the code. Your example with non existing object is not a valid case as it generates error of severity 16 and can be trapped in outer block as I demonstrated.

---

Plamen Ratchev

Answer 17

So,
the problem goes to what types of errors can NOT be caught be catch-block in the same level,but CAN BE in outer proc.
thanks.



Andrew

---

Interested in BI related technologies

Answer 18

This is described in BOL:
http://msdn.microsoft.com/en-us/library/ms175976.aspx

The following types of errors are not handled by a CATCH block when they occur at the same level of execution as the TRY…CATCH construct:

• Compile errors, such as syntax errors, that prevent a batch from running.
• Errors that occur during statement-level recompilation, such as object name resolution errors that occur after compilation because of deferred name resolution.

These errors are returned to the level that ran the batch, stored procedure, or trigger.

---

Plamen Ratchev

Answer 19

These errors are returned to the level that ran the batch, stored procedure, or trigger.

Will all these errors be caught by catch-block in outer procedure?



Thanks


Andrew

---

Interested in BI related technologies

Answer 20

Yes, because they are returned to the outer level as indicated.

---

Plamen Ratchev