Getting error "An INSERT EXEC statement cannot be nested"

Question

I've hit a problem with the insert...exec, and I can't find a solution online that will work. I have a stored procedure that retrieves data from an API. It does this by building a command line, running it through xp_cmdshell, and capturing the output to a table (using an insert...exec).

The stored procedure works perfectly, and formats the required data into a nice table

I'm now trying to implement this into my db, but this needs to be called from a number of other stored procedures. They need to be able to see the results of the initial stored procedure, but I've hit a "An INSERT EXEC statement cannot be nested" error, and it won't let me capture the output

I've tried various solutions I've seen suggested online, but so far none of them have worked. The initial stored procedure is calling a command line, so I can't find any other way to call it and capture the output, other than using an insert.....exec, but I still need the formatted output.

I have tried to convert my stored procedure to a function, but I cannot run the xp_cmdshell. I've also looked at getting the initial stored procedure to return the table as an output parameter (even if I create it with a type), but the stored procedure won't allow that

I've also looked at using openset, but I need to be able to pass a parameter to the stored procedure, and I don't think openset will allow this. What could I try next?

EDIT: I've put together a simple example of what I'm trying to do. The stored procedure is retrieving data from a command line. I'm just using an echo command to fudge the data, but in reality, this command line is calling an API, and receiving JSON back. The JSON is then formatted into a SQL table, and output. As this is an API call, I can't see any other way to do it without an insert...exec xp_cmdshell, but this means I cannot capture the output of the stored procedure and use it

create procedure usp_retrieveAPIdata

    @inparameter int

as

begin

    declare @APIcall varchar(200)

    --this would normally be an API call, returning a JSON array
    set @APICall='echo f1:"foo" & echo f2:"bar" & echo f1:"Hello" & echo f2:"World"'

    declare @resulttable table
    (outputfield varchar(100),ID int identity)

    insert into @resulttable
    exec xp_cmdshell @APICall

    declare @formattedtable table
    (field1 varchar(100),field2 varchar(100))

    declare @rownum int =0
    declare @field1 varchar(100)
    declare @field2 varchar(100)
    declare @currentfield varchar(100)

    while exists (select * from @resulttable where ID>@rownum)
    begin
        set @rownum=@rownum+1
        select @currentfield=outputfield from @resulttable where ID=@rownum

        if @currentfield like 'f1%'
        begin
            set @field1=replace(@currentfield,'f1:','')
        end

        if @currentfield like 'f2%' and @rownum<>1
        begin
            set @field2=replace(@currentfield,'f2:','')
            insert into @formattedtable (field1,field2) values (@field1,@field2)
        end
    end

    select * from @formattedtable
end

go

declare @resulttable table (field1 varchar(100),field2 varchar(100))

insert into @resulttable
exec usp_retrieveAPIdata 1

Answer 1

I think I've cracked it. Weird that you spend all afternoon looking at SQL, then the answer comes to you when you are cleaning out a fish tank

I need to split my sproc into two. The first part calls the API, and receives the answer as a JSON array. JSON is basically text, so rather than convert this into a table, I should just return in as an NVARCHAR(MAX) to the calling sproc.

The calling sproc can then call a second sproc to format this JSON into a table format.

As the first sproc isn't returning a table, SQL won't care about the nested Insert...exec, and as the second sproc isn't using a cmdshell, it doesn't need an insert...exec, so it can receive the results into a table

Here is the above example, but with the sproc split into 2...

begin tran

go

create procedure usp_retrieveAPIdata

    @inparameter int,
    @resultstring varchar(max) output

as

begin

    declare @APIcall varchar(200)

    --this would normally be an API call, returning a JSON array
    set @APICall='echo f1:"foo" & echo f2:"bar" & echo f1:"Hello" & echo f2:"World"'

    declare @resulttable table
    (outputfield varchar(100),ID int identity)

    insert into @resulttable
    exec xp_cmdshell @APICall

    set @resultstring=''

    select @resultstring=@resultstring + isnull(outputfield,'') + '¶' from @resulttable order by ID     --using '¶' as a random row delimiter


end
go



create procedure usp_formatdata (@instring varchar(max))

as 
begin

    print @instring

    declare @resulttable table
    (outputfield varchar(100),ID int)

    insert into @resulttable (outputfield,ID)
    select value,idx+1 from dbo.fn_split(@instring,'¶');



    declare @formattedtable table
    (field1 varchar(100),field2 varchar(100))

    declare @rownum int =0
    declare @field1 varchar(100)
    declare @field2 varchar(100)
    declare @currentfield varchar(100)

    while exists (select * from @resulttable where ID>@rownum)
    begin
        set @rownum=@rownum+1
        select @currentfield=outputfield from @resulttable where ID=@rownum

        if @currentfield like 'f1%'
        begin
            set @field1=replace(@currentfield,'f1:','')
        end

        if @currentfield like 'f2%' and @rownum<>1
        begin
            set @field2=replace(@currentfield,'f2:','')
            insert into @formattedtable (field1,field2) values (@field1,@field2)
        end
    end

    select field1,field2 from @formattedtable
end

go




declare @resulttable table (field1 varchar(100),field2 varchar(100))
declare @outstring varchar(max)


exec usp_retrieveAPIdata 110,@resultstring=@outstring output


insert into @resulttable
exec usp_formatdata @outstring

select  * from @resulttable

rollback

Many thanks to everyone who took the time to contribute to this thread

Answer 2

This is the problem with INSERT EXEC I have run into this many times over the years. Here are a few options - none of them are perfect, each has it's pros/cons but should help get you across the finish line nonetheless.

Sample Procs:

USE tempdb
GO

-- Sample Procs
CREATE PROC dbo.proc1 @a INT, @b INT
AS
  SELECT x.a, x.b
  FROM  (VALUES(@a,@b))          AS x(a,b)
  CROSS JOIN (VALUES(1),(2),(3)) AS xx(x);
GO

CREATE PROC dbo.proc2 @a INT, @b INT
AS
  DECLARE @x TABLE (a INT, b INT);

  INSERT @x(a,b)
  EXEC dbo.proc1 5,10;

  SELECT x.a, x.b FROM @x AS x;

This will fail due to nesting INSERT EXEC:

DECLARE @a INT = 2, @b INT = 4;

DECLARE @t2 TABLE (a INT, b INT);
INSERT  @t2(a,b)
EXEC dbo.proc2 5,10;

Option #1: Extract the stored procedure logic and run it directly

Here I'm simply taking the logic from dbo.proc2 and running it ad-hoc

DECLARE @t2 TABLE (a INT, b INT);
DECLARE @a INT = 2, @b INT = 4;

INSERT @t2 (a,b)
  -- Logic Extracted right out of dbo.proc1:
  SELECT x.a, x.b
  FROM       (VALUES(@a,@b))     AS x(a,b)
  CROSS JOIN (VALUES(1),(2),(3)) AS xx(x);

SELECT t2.* FROM @t2 AS t2;

Option #2 - Extract the proc logic and run it as Dynamic SQL

DECLARE @t2  TABLE (a INT, b INT);
DECLARE @a   INT = 2, 
        @b   INT = 4;
DECLARE @SQL NVARCHAR(4000) = N'
    SELECT     x.a, x.b
    FROM       (VALUES(@a,@b))     AS x(a,b)
    CROSS JOIN (VALUES(1),(2),(3)) AS xx(x);',
  @ParmDefinition NVARCHAR(500) = N'@a INT, @b INT';

INSERT @t2    
EXEC sys.sp_executesql @SQL, @ParmDefinition, @a=@a, @b=@b;

SELECT t2.* FROM @t2 AS t2; -- validation

Option #3 - option #2 with the proc code directly from metadata

DECLARE @t2  TABLE (a INT, b INT);
DECLARE @a   INT = 2, 
        @b   INT = 4;

DECLARE 
  @SQL NVARCHAR(4000) = 
    ( SELECT SUBSTRING(f.P, CHARINDEX('SELECT',f.P),LEN(f.P))
      FROM   (VALUES(OBJECT_DEFINITION(OBJECT_ID('proc1')))) AS f(P)),
   @ParmDefinition NVARCHAR(500) = N'@a INT, @b INT';

EXEC sys.sp_executesql @SQL, @ParmDefinition, @a=@a, @b=@b;

The downside here is parsing out what I need. I made my example simple with the logic beginning with a SELECT clause, the real world is not as kind. The upside, compared to manually adding the logic, is that your code will be up-to-date. Changes to the proc automatically change your logic (but can also break the code).

Option #4: Global Temp Table

I haven't really tried this but it should work. You could re-write the proc (proc2 in my example) like this:

ALTER PROC dbo.proc2 @a INT, @b INT, @output BIT = 1
AS
  IF OBJECT_ID('tempdb..##x','U') IS NOT NULL DROP TABLE ##x;
  CREATE TABLE ##x(a INT, b INT);

  INSERT ##x(a,b)
  EXEC dbo.proc1 5,10;

  IF @output = 1
  SELECT x.a, x.b FROM ##x AS x;
GO

I am populating a global temp table with the result set then adding an option to display the output or not. When @output = 0 the result-set will live in ##x, which can be referenced like so:

DECLARE @t2 TABLE (a INT, b INT);

EXEC dbo.proc2 5,10,0;
INSERT  @t2(a,b)
SELECT * FROM ##x;

SELECT * FROM @t2;