Catch block not being executed

A QA tester in my company noticed that the catch block in an insert..exec statement is incorrectly attributing the error to the outer procedure instead of an inner procedure.

The inner procedure is being called in several different places in various stored procedures. It’s working fine, but I was tasked with adding error logging to the inner procedure. We are having the issue where the error is being attributed to the outer procedure. We both think that this is incorrect behavior. Is there a way to get the CATCH block in the inner procedure to work?

DROP PROC IF EXISTS firstproc;
DROP PROC IF EXISTS secondproc;
DROP TABLE IF EXISTS errortable;
DROP TABLE IF EXISTS mytable;

--Table to hold divide by zero data

CREATE TABLE mytable (
    A DECIMAL
    ,B DECIMAL
    )

INSERT INTO mytable
VALUES (
    1
    ,1
    )
    ,(
    1
    ,0
    )
GO

--Table to hold error proc name

CREATE TABLE ErrorTable
(procname NVARCHAR(255));
GO

--Inner Proc that creates a divide by zero error. 
--I want the catch statement to get executed here so that I can tell there was an error.

CREATE PROCEDURE [dbo].[FirstProc]
AS
BEGIN
    BEGIN TRY
        SELECT A / B
        FROM mytable;
    END TRY

    BEGIN CATCH
      INSERT Errortable VALUES (OBJECT_NAME(@@PROCID)+ ' has an error.');
      THROW;
    END CATCH
END
GO

--Outer proc that calls inner proc. The error is attributed to this proc in the error table.



CREATE PROCEDURE [dbo].[SecondProc]
AS
BEGIN
    BEGIN TRY
            CREATE TABLE #mytable
            (C DECIMAL  )

        INSERT INTO #mytable
        EXEC FirstProc
    END TRY

    BEGIN CATCH
      INSERT Errortable VALUES (OBJECT_NAME(@@PROCID)+ ' has an error.');
      THROW;
    END CATCH
END
GO

EXEC secondproc;
GO

--See that the error is attributed to the outer proc, not the inner proc that has the error.

SELECT * FROM errortable


--Repeat the steps from above, except remove the insert in the insert..exec.

DROP PROC IF EXISTS firstproc;
DROP PROC IF EXISTS secondproc;
DROP TABLE IF EXISTS mytable;
DROP TABLE IF EXISTS errortable;
GO

--Table to hold divide by zero data

CREATE TABLE mytable (
    A DECIMAL
    ,B DECIMAL
    )

INSERT INTO mytable
VALUES (
    1
    ,1
    )
    ,(
    1
    ,0
    )
GO

--Table to hold error proc name

CREATE TABLE ErrorTable
(procname NVARCHAR(255));
GO

--Inner Proc that creates a divide by zero error. 
--I want the catch statement to get executed here so that I can tell there was an error.

CREATE PROCEDURE [dbo].[FirstProc]
AS
BEGIN
    BEGIN TRY
        SELECT A / B
        FROM mytable;
    END TRY

    BEGIN CATCH
      INSERT Errortable VALUES (OBJECT_NAME(@@PROCID)+ ' has an error.');
      THROW;
    END CATCH
END
GO

-- Second Proc that calls the first proc. This one has the insert statement commented.

CREATE PROCEDURE [dbo].[SecondProc]
AS
BEGIN
    BEGIN TRY
            CREATE TABLE #mytable
            (C DECIMAL  )

        --INSERT INTO #mytable
        EXEC FirstProc
    END TRY

    BEGIN CATCH
      INSERT Errortable VALUES (OBJECT_NAME(@@PROCID)+ ' has an error.');
      THROW;
    END CATCH
END
GO

EXEC secondproc;

GO

--The error is attributed at both the inner and the outer proc level.
SELECT * FROM errortable;

DROP PROC IF EXISTS firstproc;
DROP PROC IF EXISTS secondproc;
DROP TABLE IF EXISTS errortable;
DROP TABLE IF EXISTS mytable;

https://dbfiddle.uk/?rdbms=sqlserver_2019&fiddle=0309ce5d61a4e700b6818d0f132ab2de

Answer

This is an intended behaviour.

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.

Object name resolution errors

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

https://learn.microsoft.com/en-us/sql/t-sql/language-elements/try-catch-transact-sql?view=sql-server-ver15#errors-unaffected-by-a-trycatch-construct

If you want to handle missing object error in the [FirstProc] check object existance, raise error in your code.

CREATE PROCEDURE [dbo].[FirstProc]
AS
BEGIN
    BEGIN TRY
       declare @n varchar(200) = 'sys.objects_not_real_table';
       if (OBJECT_ID(@n, 'U')  is null) 
       begin
           set @n += ' is missing';
           raiserror(@n, 16, 1);
           --  or throw:
           --throw 50001, @n, 1;
        end
        SELECT name
        FROM sys.objects_not_real_table;
    END TRY
    BEGIN CATCH
        SELECT 'Error from First Proc: ' + ERROR_MESSAGE();
    END CATCH
END

db<>fiddle