SQL: Combine Multiple Dynamic Result Sets in One Stored Procedure
We have a little problem and can use some help - we need to combine multiple result sets from one stored procedure into one result set (a limitation of our Java reporting framework). We looked at Union and so on, but the problem is that there are multiple crosstab results in the stored procedure search results, and (1) the number of columns for each result set is unknown, and (2) the column names for each result set are unknown.
Basically if sp_ has 3 results like:
Identity name
1 Sam
2 Time
ID FName LName
1 John James
2 Test by type
3 Sam Hopkins
Identification amount
1 1000
2 5000
The ideal result would basically return the above text as is, which our framework would print for the user. Also note that these 3-4 result sets are not related to each other.
We are using SQL Server 2000 and Java 1.4.
Any advice would be appreciated.
Thanks, SP
PS: Alternative explanation in case 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}
The results are then displayed in three separate sets of result fields. We want these result objects to be returned as a single result set using the stored procedure (without knowing the column number / name due to cross notation). Thank.
source to share
Your question does not specify which database vendor or what client application framework you are using, but most saved process databases have the ability to return multiple result sets and several client frameworks that I am familiar with (VB6, C ++,. NET and etc.), Everyone has the ability to retrieve these multiple result objects from a single database access and manipulate them to do just about anything you might need ...
based on your comment, if your reporting structure can be hardcoded to generate column headers (firstName, lastName, amount, etc.) without getting those rows from the database, you can 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 selection must be the same (3 in this example) and their names (aliases) and data types must be the same ...
source to share