Stored Procedure to return results from multiple queries

[TonyBoex]

Is there a way to have a stored procedure return the results of approximately 50 queries as a single recordset? Each query returns the number of records matching a particular parameter and I need the results of 50 queries to populate a results table on an ASP page. The individual queries are all similar to the following:

SELECT COUNT(MishapID) AS CRW --CRW is the variable string
FROM dbo.tblMishaps
WHERE (MishapID IN
(SELECT DISTINCT tblMishaps.MishapID
FROM tblMishaps, tblFactors, tblMishapFactors
WHERE tblMishaps.MishapID = tblMishapFactors.MishapID_FK
AND
tblFactors.[3rdLevelCode] = tblMishapFactors.[3rdLevelCode_FK]
AND (tblFactors.[2ndLevelCode] = 'CRW')))

How do I return all the results to the calling ASP page, individually or as a combined recordset?

 

[Cagliostro]

use union
select* from xxx
union
select* from yyy John Fill

ivfmd@mail.md

 

[TonyBoex]

I tried using UNION but the result was a single column recordset. If possible I'd like to return a single record having 50 columns, each having a column name which corresponds to the text string criteria (ie. CRW). Thanks for the help.

 

[Cagliostro]

you want to do some thing like a crosstab query? John Fill

ivfmd@mail.md

 

[TonyBoex]

I'll read up on Crosstab queries. Thanks for the suggestion.

 

[Cagliostro]

As far as I know SQL Server doesn't support crosstab queries like access. You can do some work around. John Fill

ivfmd@mail.md