Search code examples
sqlsql-serverinsert-into

Avoiding "An INSERT EXEC statement cannot be nested"

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

Solution

  • 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