SSRS returning only child nested stored procedure data

Question

Within SSRS, I am creating a dataset by calling a stored procedure which calls a child stored procedure and so on. There are 4 nested stored procedures. Each calling stored procedure has a parameter to avoid displaying the data, but rather embeds it in a global temp table. This allows me to display the data only from the parent stored procedure.

Even with the concern for dependency issues within stored procedures, I am doing this so I can:

Re-use the stored procedure logic as each parent stored procedure builds on the data from the child stored procedure
Retrieve different types of data depending on which embedded child stored procedure I am calling.
Avoid repeating the same logic as each stored procedure builds on its 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 the data

And the stored procedure ClaimantFinancePaymentCategories calls another 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 the data

And so on. In SSRS, the parent dataset is returned when I run the "Query Designer", but the SSRS displays the data columns from the parent stored procedure when a parent stored procedure only has one embedded stored procedure.

Unfortunately after calling 2 nested children stored procedures, SSRS displays the data columns from the most embedded child stored procedure and not the data columns from the parent stored procedure.

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

Any suggestions?

StevenWhite StevenWhite · Accepted Answer · 2016-02-09T22:55:41

In your report dataset you can declare a temp table with the correct columns. Insert the results of the parent stored procedure into it like this:

INSERT INTO #tempTable 
EXECUTE sp_executesql @SqlString ,@ParamDef, @param1 = 1

Then select * from your temp table. This will allow the report to populate the correct columns.

SSRS returning only child nested stored procedure data

1 Answers