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

Your question is clear.But what u r trying to do is not clear.So may be you won't get optimum solution.What your script look like ? — KumarHarsh, Dec 09 '19 at 04:03

score 4 · Answer 1 · answered Dec 06 '19 at 10:09

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')

score 2 · Accepted Answer · answered Dec 06 '19 at 18:52

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

answered Dec 06 '19 at 18:52

Lukasz Szozda

162,964
23
234
275

1

I was sure I've tried that and it didn't work back then. But it looks like it indeed works well. I'm not using this in my production code now, but it's good to have an answer anyway. – Roman Pekar Dec 09 '19 at 15:01

score 0 · Answer 3 · answered Dec 09 '19 at 12:25

I am using OBJECT_ID to check the existence of the temporary table inside the inner procedure to make sure that, we are not having nested insert execute scenario.

When we check that the table is not existing only, we create the table and insert into the table.

I have provided sample code below for your reference.

--Procedure for INSERT...EXEC
CREATE PROCEDURE ValuesGetForInsert
AS
BEGIN
    SELECT 1
    UNION ALL
    SELECT 2
    UNION ALL
    SELECT 3;
END
GO

--Outer Insert Procedure
CREATE PROCEDURE dbo.OuterInsert
AS
BEGIN
IF OBJECT_ID('tempdb..#Table1') IS NULL
BEGIN
    CREATE TABLE #Table1(a int);
    INSERT INTO #Table1(a)
    EXECUTE ValuesGetForInsert;
END
    EXECUTE InnerInsert;

    SELECT 'OuterInsert',* FROM #Table1;
END
GO

--Inner Insert Procedure
CREATE PROCEDURE dbo.InnerInsert
AS
BEGIN
    IF OBJECT_ID('tempdb..#Table1') IS NULL
    BEGIN
        CREATE TABLE #Table1(a int);
        INSERT INTO #Table1(a)
        EXECUTE ValuesGetForInsert;
    END
    SELECT 'Inner Insert', * FROM #Table1
END
GO

--Executing outer insert or inner insert indepenently
EXECUTE dbo.OuterInsert;
EXECUTE dbo.InnerInsert;
GO

This only works if you control the outer and inner inserts and know they use the same table names - at which point the inner procedure can just be passed a parameter to tell it whether it can use `INSERT...EXEC`, rather than trying to detect the situation. — Damien_The_Unbeliever, Dec 09 '19 at 13:17

Avoiding "An INSERT EXEC statement cannot be nested"

3 Answers3