multiple select in one sp

I was wondering if it's possible to have a stored procedure that hastwo(or more) select statements which you can combine as a single result set.I am not able to use union as my select returns different number of columns.My selects are like this,This example is using only two table but i can have more then 2 tables in situation..

ELECT SUM(col1) AS sumcol1
FROM tbl1
WHERE (ID = @.para1')

SELECT SUM(col1) AS sumcol1, SUM(col2) AS sumcol2, SUM(col3)
AS sumcol3, SUM(col4) AS sumcol1
FROM tbl2
WHERE (id = @.para1).

thanks

Sure you can. Each resultset will form a new DataTable in a DataSet, or if you are using a DataReader, you can access each resultset in turn by calling the DataReader.NextResult() method. Bear in mind that this won't work with a SqlDataSource control. It is only capable of filling one DataTable. I forget whether it keeps just the first resultset or the last one.

|||

Mikesdotnetting:

Sure you can. Each resultset will form a new DataTable in a DataSet, or if you are using a DataReader, you can access each resultset in turn by calling the DataReader.NextResult() method. Bear in mind that this won't work with a SqlDataSource control. It is only capable of filling one DataTable. I forget whether it keeps just the first resultset or the last one.

Technically speaking, the method you propose returns two separate result sets.

Both can be accessed from C#/VB, but they are separate result sets.

If a single result set is needed, then pad out the two queries with innocuous (sp?) dummy columns and union all them together.

|||

david wendelken:

Mikesdotnetting:

Sure you can. Each resultset will form a new DataTable in a DataSet, or if you are using a DataReader, you can access each resultset in turn by calling the DataReader.NextResult() method. Bear in mind that this won't work with a SqlDataSource control. It is only capable of filling one DataTable. I forget whether it keeps just the first resultset or the last one.

Technically speaking, the method you propose returns two separate result sets.

That's right. That's because I thought that was what the questioner wanted. Now I've re-read the original question, I can see it isn't.

Doh.

|||

Thanks David,i got what you are saying but how do you dummy out the columns?Sorry, for these lame questions...

|||

nb123:

Thanks David,i got what you are saying but how do you dummy out the columns?Sorry, for these lame questions...

cast(null as int) as dummy_int
cast(null as varchar(6)) as dummy_varchar6

or just use 0 or '' if zero or an empty space works better for you

|||

Thanks David,it works