Search code examples
sql-serverstored-proceduresreporting-servicesssrs-2008

SSRS with Stored Procedure containing multiple count statements


I have few lines query which has multiple count statements. I have created Stored procedure for the select count statements and it is giving results when i run it on SSMS but when i use the stored procedure in SSRS it is giving only 1st query output. How do I get other queries output too while using ssrs?

Code from Comments:

select count(*) as Field1
from #TempTable1;

select count(*) as Field2
from #TempTable2;

select count(distinct ANo) as F3
from #TempTable1;

select count(distinct BNo) as F4
from #TempTable2;

select count(*) as F5
from Table1
where InsertDate >= StartDate
      and InsertDate <= EndDate
      and value1 >= 2
      and TestField = 0;

select count(*) as F6
from Table1
where InsertDate >= StartDate
      and InsertDate <= EndDate
      and value1 >= 2
      and TestField = 1;

select count(*) as F7
from Table1
where InsertDate >= StartDate
      and InsertDate <= EndDate
      and value1 >= 2
      and TestField = 0;

Solution

  • Finally Found the answer

    Here is what I've done Declared local variables and used them as below in query

    select @A= count() from Table1 Select @B= count() from Table 2

    Select @A as Field1,@B as Field2

    This gives the output in single row.