How to resolve An INSERT EXEC statement cannot be nested in SQL server

Sunil Keshari picture Sunil Keshari · Oct 9, 2017 · Viewed 9.1k times · Source

I have a sp in which I am returning one single column result. I am trying to store the result into a table type, but I am getting this error:

An INSERT EXEC statement cannot be nested.

I have googled around but didn't find any acceptable solution.

The sp is as follows:-

    ALTER PROCEDURE [dbo].[Sp_DemographicFilter_booster]
        (

        @FilterSelected FilterSelected READONLY, 
        @CountryCategoryId int=null
    )
AS
BEGIN
    SET NOCOUNT ON;

    DECLARE @WhereCondition varchar(500)  ;
    DECLARE @QueryString Varchar(MAX) ;
    DECLARE @QueryString_booster Varchar(MAX) ;
    DECLARE @Filter table (FilterColumn Varchar(200),FilterValue Varchar(200))
    DECLARE @Result table (SERIAL int)
    DECLARE @Result_booster table (SERIAL int)

    if( select top 1  FilterColumn FROM  @FilterSelected where FilterColumn<>'HISPANIC') is NOT NULL
    BEGIN
    Insert into @Filter
    Select * from @FilterSelected where FilterColumn<>'HISPANIC'
    --DECLARE @DemoTbl TABLE (MetricName VARCHAR(100),CatValue VARCHAR(100))

        SELECT @WhereCondition= COALESCE( @WhereCondition + ' and ', '')+SubjectList FROM (
        SELECT DISTINCT STD.Filtercolumn +' in ('+
        ISNULL(STUFF((SELECT ', '+'''' + ssm.Filtervalue+''''
        FROM @Filter SSM
        INNER JOIN @Filter SUB ON SUB.FilterColumn = SSM.FilterColumn and SUB.FilterColumn=STD.FilterColumn
        WHERE sub.FilterValue = ssm.FilterValue
        FOR XML PATH('')
        ), 1, 1, ''), 'Not Assigned Yet')+')' AS SubjectList
        FROM @Filter STD)A


        print @WhereCondition

    --INSERT INTO @DemoTbl
    --select SUBSTRING(col1,1, CHARINDEX(':',col1,1)-1) MetricName,SUBSTRING(col1, CHARINDEX(':',col1,1)+1,LEN(Col1)) CatValue  
    --from dbo.UF_CSVDataToTable(@FilterSelectedSelected)


    SET @QueryString='SELECT SERIAL FROM Logical.Demographic D
    WHERE '+@WhereCondition+' and CountryCategoryId='+cast(@CountryCategoryId as varchar(10))
    PRINT @QueryString
    insert into @Result
    EXEC(@QueryString)
    --select * from @Result
    END
     IF(select top 1  FilterColumn FROM  @FilterSelected where FilterColumn='HISPANIC') IS NOT NULL
    BEGIN

    Delete from @Filter;

    DECLARE @Response varchar(20)=null;

    Insert into @Filter
    Select * from @FilterSelected where FilterColumn='HISPANIC'

    select @Response=FilterValue from @Filter;

    DECLARE @VariableID int=null;

    select @VariableID=SurrogateKeyCounter from MetaData.Metadata_Screener where DBMetricName='HISPANIC';

    SET @QueryString_booster='SELECT SERIAL FROM Logical.Response R
    WHERE variableid='+cast(@VariableID as varchar(10))+' and CountryCategoryId='+cast(@CountryCategoryId as varchar(10))
    +' and ResponseName='''+@Response+''''

    PRINT @QueryString_booster
    Insert into @Result_booster
    EXEC(@QueryString_booster)

    END
    DECLARE @Final_Result table (SERIAL int)

    insert into @Final_Result
    select * from @Result
    UNION
    select * From @Result_booster

    select * from @Final_Result


    END

I am calling this procedure like this:

declare @ds FilterSelected
insert into @ds values('Hispanic','yes')
@FilterSelected=@ds,@CountryCategoryId=100

 DECLARE @DemoTbl TABLE (Serial INT)


Insert into @DemoTbl    
EXEC Sp_DemographicFilter_booster @FilterSelected=@ds,
@CountryCategoryId=100

Answer

Mahesh Parchure picture Mahesh Parchure · Sep 28, 2020

Call SP Sp_DemographicFilter_booster along with unique ID . Inside Sp_DemographicFilter_booster SP create global table (##) stored result in global table with same unique ID AS ID field Now when return to main SP access global table with where condition that unique ID