SQL: Combine multiple dynamic resultsets in one stored procedure

Question

Hi Folks,

We're having a small issue and could use some help - we have the need to combine multiple resultsets from one stored procedure into one resultset (the limitation of our Java reporting framework). We've looked at Union, etc. but the problem is that the stored procedure resultsets are multiple crosstab results and the (1) number of columns for each resultset are unknown and (2) the column names for each resultset are unknown.

Basically, if the sp_ has 3 results like:

ID Name

1 Sam

2 Time

ID FName LName

1 John Jacob

2 Tim Test

3 Sam Hopkins

ID Amount

1 1000

2 5000

The ideal result would basically return the above text as-is which our framework would print to the user. Also please note that these 3-4 resultsets are not related to each other.

We're using SQL Server 2000 and Java 1.4.

Any advice would be appreciated.

Thanks, SP

PS: An alternative explaination in case the one above is not very clear. In SQL Query Analyzer if we have 3 select statements:

select * from countries; {returns id,countryname,countrycode}

select * from people; {id,countryname,countrycode}

select * from balance; {id,countryname,countrycode}

Then the results are displayed in three separate resultset boxes. We need to these resultsets to be returned as one resultset by the stored procedure (while not knowing the number/name of the columns due to the crosstab-ing taking place). Thanks.

Answer 1

Your question does not specify which database vendor, or which client application framework you are using, but most databases with stored procs have the capability to return multiple resultsets, and the few client frameworks I am familiar with (VB6, C++, .Net, etc.) all have the capability to retrieve these multiple resultsets from a single database access and manipulate them to do just about whatewver you might want...

based on your comment, if your reporting framework can be hard coded to generate the column headings (firstName, lastName, amount, etc) without getting these strings from the database, then you could do this:

  Select ID, Name as value1, null as value2
  From TableA 
    Union
  Select ID, FName as value1, LName as value2
  From TableB 
    Union
  Select ID, Cast(Amount as VarChar(20)) as value1, null as value2
  From TableC

The key is that the number of columns returned by each select must be the same (3 in this example) and their names (aliases) and datatypes must be the same as well...

Answer 2

if the ids from the different tables are related, then your t-SQL should be left joins.