Select statement on non-existing columns not being caught by exception block

Question

I'm expecting an error here since X_TEST column doesn't exists. But the error is not being caught by the exception block.

BEGIN TRY
   SELECT X_TEST FROM ACCOUNTS 
END TRY
BEGIN CATCH
   PRINT 'There was an error! ' + ERROR_MESSAGE()
END CATCH

Why ? Is it because of the severity of the error ?

Answer 1

No, TRY can only handle certain types of errors within its own scope. Now, you say you have a stored procedure that looks like this (after non_existent_column has been dropped):

CREATE PROCEDURE dbo.blat
AS
BEGIN
  BEGIN TRY
    SELECT non_existent_column FROM dbo.table_that_exists;
  END TRY
  BEGIN CATCH
    PRINT ERROR_MESSAGE();
  END CATCH
END
GO

If you simply do this...

EXEC dbo.blat;

...you will get hammered with a compilation error because the statement within that TRY's scope fails to parse:

Msg 207, Level 16, State 1, Procedure fooblat, Line 5
Invalid column name 'non_existent_column'.

However you can catch this error at an outer scope (whether the procedure has TRY/CATCH or not):

BEGIN TRY
  EXEC dbo.fooblat;
END TRY
BEGIN CATCH
  PRINT 'There was an error:';
  PRINT ERROR_MESSAGE();
END CATCH

Result (notice this is not an exception and the text is no longer red):

There was an error:
Invalid column name 'non_existent_column'.

You can also avoid this with dynamic SQL, but it seems like you've already shot that down, so this is the next best suggestion I can think of - capture the error at an outer scope.