Calling a nested stored procedure

[Earl]

Realizing this isn't the server forum, I've read that my issue is one of how
the code is calling the procedure, thus the question here. The issue is that
one of my stored procedures executes three other stored procedures. I use a
command object to pass in the parameters, but I only receive the first
resultset back. Because?

 

[William \(Bill\) Vaughn]

How are you executing the SP? With Fill or ExecuteReader or how? How are you
testing for the next resultset(s)?

--
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
Microsoft MVP
www.betav.com
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________

 

[Earl]

Fill. I'm using the data for a report, so what I really want is a SINGLE
resultset without nesting more subqueries (the main sproc is almost 100
lines now and frankly, I'm getting a little cross-eyed from all the
parenthesis, so I'd rather reduce it rather than expand it). Thus I nested
ALL the sprocs inside of one (with the catchy title
"GetSalesReportProcedures"). However, this results in multiple resultsets --
but I need to tie them all into the same datatable because I bind the
datatable directly to a subreport. Thus to clarify my question, I need just
ONE resultset from a nested set of sprocs, instead of the multiple
resultsets that I'm getting.

 

[William \(Bill\) Vaughn]

Ok, Fill should do the trick but only if the SP returns a single rowset. It
knows how to step over "empty" resultsets (those that don't contain rowsets)
but it builds a new table for each rowset returned. Did you check to see
what the Dataset.Tables.Count property is? If it's more than one, you're
getting more than one rowset.
One approach you might consider is to Merge the DataTables after they
arrive.

Are you using Reporting Services to build the reports?


--
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
Microsoft MVP
www.betav.com
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________

 

[Earl]

The single SP exec'ing other SPS is returning multiple rowsets -- that is
indeed the issue I'm trying to resolve. I'm at the point where I'm
considering changing the sps into multiple views and combining them into one
temp table with SELECT INTO in order to get the single rowset and thus
single datatable. Is this the best approach? Or would filling multiple
adapters and using a data relation be the better way to go?

I'm using ActiveReports.Net to build the reports. I don't really have any
problems with the report designer.

 

[William \(Bill\) Vaughn]

There are several approaches. Fill multiple tables and merge them into a
single table or use SELECT INTO, but this is more complex as you might have
persistence problems with #temp tables but this is also a viable approach.

--
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
Microsoft MVP
www.betav.com
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________

 

[Kevin Yu [MSFT]]

Hi Earl,

First of all, I would like to confirm my understanding of your issue. From
your description, I understand that you need to run an SP and make it
return a single resultset to the DataSet. If there is any misunderstanding,
please feel free to let me know.

I agree with Bill that there are several approaches to this issue. To merge
the results in the DataSet to one table or to use multiple DataAdapters to
fill the resultsets in to a single DataTable. However, you can also try to
use UNION operator in TSQL which combines the results of two or more
queries into a single result set consisting of all the rows belonging to
all queries in the union. For more information, please check the following
link for UNION operator:

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_
sa-ses_9sfo.asp

HTH.

Kevin Yu
=======
"This posting is provided "AS IS" with no warranties, and confers no
rights."

 

[Earl]

Thanks Kevin and Bill. I will consider the UNION operator.