Geek asks and answers

Select instructions for non-existent columns that are not blocked by the exception block

I am expecting an error here since the X_TEST column does not exist. But the error is not blocked 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?

+3
source share
3 answers

No, TRY can only handle certain types of errors in its area. Now you say that you have a stored procedure that looks like this (after it non_existent_columnwas deleted):

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 just do it ...

EXEC dbo.blat;

... you will get a clogged compilation error because the statement inside this TRY area cannot parse:

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

However, you can catch this error in the external area (whether the procedure has it TRY/CATCHor not):

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

( , , ):

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

SQL, , , , , - .

+3

. .

:

BEGIN TRY
   sdgedtju§$%&/()= 
END TRY
BEGIN CATCH
   PRINT 'There was an error! ' + ERROR_MESSAGE()
END CATCH

, , ? .

+2

This code will give you the behavior you expected to see ...

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

More articles:


All Articles