sql server - SSRS returning only child nested stored procedure data -

within ssrs, creating dataset calling stored procedure calls child stored procedure , on. there 4 nested stored procedures. each calling stored procedure has parameter avoid displaying data, rather embeds in global temp table. allows me display data parent stored procedure.

even concern dependency issues within stored procedures, doing can:

1. re-use stored procedure logic each parent stored procedure builds on data child stored procedure
2. retrieve different types of data depending on embedded child stored procedure calling.
3. avoid repeating same logic each stored procedure builds on children

for example:

declare @sqlstring nvarchar(2000) declare @paramdef nvarchar(2000)  set @sqlstring = n'exec claimantfinancepaymentcategories @param1' set @paramdef = n'@param1 int'  execute sp_executesql @sqlstring ,@paramdef, @param1 = 1 -- view data 

and stored procedure claimantfinancepaymentcategories calls nested stored procedure:

declare @sqlstring nvarchar(2000) declare @paramdef nvarchar(2000)  set @sqlstring = n'exec claimantfinancepaymentsubcategories @param1' set @paramdef = n'@param1 int'  execute sp_executesql @sqlstring ,@paramdef, @param1 = 0 -- view data 

and on. in ssrs, parent dataset returned when run "query designer", ssrs displays data columns parent stored procedure when parent stored procedure has 1 embedded stored procedure.

unfortunately after calling 2 nested children stored procedures, ssrs displays data columns embedded child stored procedure , not data columns parent stored procedure.

i tried returning data using user-defined table in embedded children stored procedures instead of global temp table, had same result.

any suggestions?

in report dataset can declare temp table correct columns. insert results of parent stored procedure this:

insert #temptable  execute sp_executesql @sqlstring ,@paramdef, @param1 = 1 

then select * temp table. allow report populate correct columns.