Datasets - Running procs within procs

[ThomMenzies]

I have a Proc, let's call it 'spB'. Within that proc I have a nested Proc ('spA') which goes off and inserts some records into a few tables then based on a Bit parameter decides whether to return a recordset.

Now when I execute spA from spB I pass 'False' as the paramter for spA so it will not return a dataset (it will just do the inserts) so it completes that proc and then outputs the spB data. When I set up that dataset based on spB in the 'Data' tab of SSRS and execute it there, it returns the correct results from spB.

However, when I go to the 'Layout' tab and look at the 'Datasets' collection it does not display the output fields from spB, but displays the fields from spA. Because I have passed 'False' as the 'return resultset' parameter it should not return any spA fields.

Indeed when running a simple 'Exec spA 0' it runs correctly (i.e. does not return any results) but it seems to pick up the field definition anyway and only allow me to select from them on my report when using SSRS.

Now, a couple of things I've tried. First I tested my logic by creating a test proc that simply returned GetDate() based upon a Bit parameter. The same thing happens (only one field appears in the Datasets collection on the 'Layout' tab).

Secondly I added the fields manually to the Dataset (in the Layout tab) and when previewing it does return the 'spB-only' fields so they are there. However, every time the datset is refreshed the (manually added) fields are removed.

Has anyone had any experience with this (and better yet found a solution)?

Many thanks,

Thom

 

[ThomMenzies]

I have located the problem! I'll post what it was in case someone with the same issue finds this on a search one day!

The problem was with the pre-compile of the nested proc. My bit parameter sql read a bit like this:

If @ReturnResultSet=1 Select * From Table1

but during the precompile it 'readied' the field structure of the object Table1 which ultimately ended up as being the dataset in SSRS. To get round it I had to dump the data into a temp table (which obviously could not be 'readied' during the pre-compile stage) and selected from that:

If @ReturnResultSet=1
Begin
Select * Into #Table1 From Table1
Select * From #Table1
Drop Table #Table1
End

And this solved the problem. Hope it helps!