1

BACKGROUND

I have a procedure that has INSERT INTO ... EXEC (@sql) from dynamic SQL. This procedure's results is INSERTed INTO table outside of procedure. When I attempt this, I get get an error:

[S0001][8164] An INSERT EXEC statement cannot be nested.

This error is discussed in other questions, but for inner procedures' calls instead of dynamic SQL:

Example with error:

-- =================================
-- table with test data
-- =================================

CREATE TABLE dbo.TestInsertIntoDynamicData1
(
  data nvarchar(max)
)

INSERT INTO dbo.TestInsertIntoDynamicData1
VALUES ('one1'), ('two1'), ('three1')

GO

-- =================================
-- another table with test data
-- =================================

CREATE TABLE dbo.TestInsertIntoDynamicData2
(
  data nvarchar(max)
)

INSERT INTO dbo.TestInsertIntoDynamicData2
VALUES ('one2'), ('two2'), ('three2')

GO

-- =================================
-- procedure with dynamic query
-- =================================

CREATE PROCEDURE dbo.TestInsertIntoDynamicProc
    @TableName nvarchar(100)
AS
  BEGIN
    DECLARE @Results table(
      data nvarchar(max)
    )

    DECLARE @sql nvarchar(max)
    SET @sql = '
      SELECT data
      FROM dbo.' + @TableName + ';
    '

    -- FIRST INSERT INTO ... EXEC ...
    INSERT INTO @Results  -- this INSERT is required for example
    EXEC (@sql)

    SELECT *
    FROM @Results;
  END
GO

-- =================================
-- CALL
-- =================================

DECLARE @ResultsOfProc table(
  data nvarchar(max)
)

-- SECOND INSERT INTO ... EXEC ...
INSERT INTO @ResultsOfProc (data)
EXEC dbo.TestInsertIntoDynamicProc @TableName = 'TestInsertIntoDynamicData2'

SELECT *
FROM @ResultsOfProc;

GO

DROP TABLE dbo.TestInsertIntoDynamicData1
DROP TABLE dbo.TestInsertIntoDynamicData2
DROP PROCEDURE dbo.TestInsertIntoDynamicProc

https://stackoverflow.com/a/2917775/7573844

QUESTION

How can we get around this error?

iminiki
  • 2,549
  • 12
  • 35
  • 45
Evgeny Nozdrev
  • 1,530
  • 12
  • 15

1 Answers1

0

Move INSERT INTO to dynamic query. Refactor table variable to temp table in order to use it in dynamic query. Fixed procedure:

CREATE PROCEDURE dbo.TestInsertIntoDynamicProcFixed
    @TableName nvarchar(100)
AS
  BEGIN
    CREATE TABLE #Results (  -- refactor to temp table
      data nvarchar(max)
    )

    DECLARE @sql nvarchar(max)
    SET @sql = '
      INSERT INTO #Results         -- move INSERT to here
      SELECT data
      FROM dbo.' + @TableName + ';
    '

    -- FIRST INSERT INTO ... EXEC ...
    EXEC (@sql)

    SELECT *
    FROM #Results;
  END
GO
Evgeny Nozdrev
  • 1,530
  • 12
  • 15
  • 1
    Personally, I don't think this isn't much different from the link you posted at the bottom of your question. They are still executing a sql string, you just added a parameter. No? Also, I'd use [QUOTENAME](https://learn.microsoft.com/en-us/sql/t-sql/functions/quotename-transact-sql?view=sql-server-2017) in your code. – S3S Sep 20 '19 at 14:34
  • 1
    Beside to what @scsimon said, why you even need a dynamic SQL here? Can't you just `INSERT INTO #TempTable SELECT Data FROM SourceTable`? – Ilyes Sep 20 '19 at 14:38
  • @scsimon Code in question does not work while this code does – Evgeny Nozdrev Oct 29 '19 at 11:39
  • @Sami this was made for example. In real life code is more complex. – Evgeny Nozdrev Oct 29 '19 at 11:40