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