TRY...CATCH errors into and error log table

Question

I'm having a problem with TRY...CATCH blocks. Can someone explain why the following code will not execute my sp?

DECLARE @Result int
SET @Result = 0
BEGIN TRY
    SELECT * FROM TableNoExist
END TRY
BEGIN CATCH
    SET @Result = ERROR_NUMBER()
END CATCH
EXEC dbo.spSecurityEventAdd @pSecurityEventTypeID = 11, @pResult = @Result

But this code does work:

DECLARE @Result int
SET @Result = 0
BEGIN TRY
    SELECT 1/0
END TRY
BEGIN CATCH
    SET @Result = ERROR_NUMBER()
END CATCH
EXEC dbo.spSecurityEventAdd @pSecurityEventTypeID = 11, @pResult = @Result

I'd like to make sure I catch all errors. Thanks

Answer 1

Compile and Statement-level Recompile Errors

There are two types of errors that will not be handled by TRY…CATCH if the error occurs in the same execution level as the TRY…CATCH construct:

Compile errors, such as syntax errors that prevent a batch from executing.

Errors that occur during statement-level recompilation, such as object name resolution errors that happen after compilation due to deferred name resolution.

http://msdn.microsoft.com/en-us/library/ms179296.aspx

Answer 2

It looks like this thread answers your question.