title: ERROR_PROCEDURE() returning NULL for code executed by SP_EXECUTESQL
body: <p>I have a stored procedure with a <code>TRY CATCH</code> statement and within that <code>TRY CATCH</code> I am calling another stored procedure which is throwing an error. An exception is thrown and caught however if the error is within the called stored procedure this is not shown in the <code>ERROR_PROCEDURE()</code> it is set to NULL. It seems the reason is due to Dynamic SQL being executed in the called stored procedure.</p>
  <pre><code>ALTER PROC dbo.MyError AS
  BEGIN
      SET NOCOUNT,  XACT_ABORT ON;
      BEGIN TRY
          BEGIN TRAN
              --do stuff here
              --SQL CODE
              SELECT 'HELLO' AS hello
              
              --then call sproc
              EXEC dbo.MyInnerError
          COMMIT TRANSACTION 
      END TRY 
      BEGIN CATCH 
          IF (@@TRANCOUNT &gt; 0)
             BEGIN
                ROLLBACK TRANSACTION 
             END 
          SELECT 
              ERROR_NUMBER() AS ErrorNumber,
              ERROR_SEVERITY() AS ErrorSeverity,
              ERROR_STATE() AS ErrorState,
              ERROR_PROCEDURE() AS ErrorProcedure,
              ERROR_LINE() AS ErrorLine,
              ERROR_MESSAGE() AS ErrorMessage; 
         END CATCH
  END
  GO
  
  ALTER PROC dbo.MyInnerError AS
  BEGIN
      DECLARE @SQl nvarchar(50) = 'SELECT 1/0 as DYNAMIC_FAIL';
      EXEC SP_EXECUTESQL @SQl;
  END
  
  EXEC dbo.MyError
  GO
  </code></pre>
  <p>I have tried nesting the stored procedure in its own <code>TRY CATCH</code> but this leads to <code>TRANSACTION ROLLBACK</code> issues.</p>
  <p>Is <code>ERROR_PROCEDURE()</code> NULL because it is out of scope? And is there a way to set this?</p>
  <p>It seems the reason is due to Dynamic SQL being executed in called Stored procedure. Is there a way to handle this?</p>