Avoiding "An INSERT EXEC statement cannot be nested"

Question

I know that this is not possible to nest insert ... exec statements, but still I'm interested - is there a way to check if I already have active insert ... exec to avoid actual error?

So I want to have something like this:

....
if <check if I have outer insert into exec> = 0
    insert into <#some temporary table>
    exec <stored procedure>

In other words - insert ... exec is optional, it's nice to have it, but I want to skip it if somebody tries to call my procedure with outer insert ... exec

Answer 1

As stated here

This is a common issue when attempting to 'bubble' up data from a chain of stored procedures. A restriction in SQL Server is you can only have one INSERT-EXEC active at a time. I recommend looking at How to Share Data Between Stored Procedures which is a very thorough article on patterns to work around this type of problem.

Try with OpenRowset

INSERT INTO #YOUR_TEMP_TABLE
SELECT * FROM OPENROWSET ('SQLOLEDB','Server=(local);TRUSTED_CONNECTION=YES;','set fmtonly off EXEC [ServerName].dbo.[StoredProcedureName] 1,2,3')

Answer 2

A naive approach is to use TRY/CATCH block:

BEGIN
DECLARE @isNested BIT = 0;

  BEGIN TRY
      BEGIN TRANSACTION;
      EXEC p2;
      ROLLBACK;  --COMMIT;
  END TRY
  BEGIN CATCH
    -- Msg 8164 An INSERT EXEC statement cannot be nested.
    IF ERROR_NUMBER() = 8164 SET @isNested = 1;
    ROLLBACK;
  END CATCH

SELECT @isNested;
END

db<>fiddle demo