Do you have a comment or suggestion to improve SQL Server? We’d love to hear it!

TRY-CATCH should always work

Consider this:

CREATE PROCEDURE inner_sp AS
BEGIN TRY
PRINT 'inner_sp starting'
SELECT col FROM doesnotexist
PRINT 'inner_sp stopping'
END TRY
BEGIN CATCH
PRINT 'Let''s catch the error: ' + error_message()
END CATCH
go
SET XACT_ABORT OFF
EXEC inner_sp

It's reasonable to assume that this would print:

Let's catch the error: Invalid object name 'doesnotexist'.

But in fact the output is

inner_sp starting
Msg 208, Level 16, State 1, Procedure inner_sp, Line 4
Invalid object name 'doesnotexist'

That is, the CATCH handler is not activated. This is true for all binding errors that occurs at run time. Note that this includes errors in queries that refers to a temp table created in the procedure. Here is one example:

CREATE PROCEDURE inner_sp AS
CREATE TABLE #temp (a int NOT NULL)
BEGIN TRY
PRINT 'inner_sp starting'
SELECT a.object_id FROM sys.objects WHERE object_id IN (SELECT a FROM #temp)
PRINT 'inner_sp stopping'
END TRY
BEGIN CATCH
PRINT 'Let''s catch the error: ' + error_message()
END CATCH
go
EXEC inner_sp

inner_sp starting
Msg 4104, Level 16, State 1, Procedure inner_sp, Line 5
The multi-part identifier "a.object_id" could not be bound.

It is perfectly reasonable to expect that TRY-CATCH to catch all errors that occur in a stored procedure (save for internal errors in SQL Server that terminate the connection.) This is a bug which should have been fixed long ago.

13 votes
Sign in
Check!
(thinking…)
Reset
or sign in with
  • facebook
  • google
    Password icon
    Signed in as (Sign out)

    We’ll send you updates on this idea

    Erland Sommarskog shared this idea  ·   ·  Flag idea as inappropriate…  ·  Admin →

    1 comment

    Sign in
    Check!
    (thinking…)
    Reset
    or sign in with
    • facebook
    • google
      Password icon
      Signed in as (Sign out)
      Submitting...

      Feedback and Knowledge Base