get the number of records a stored procedure returns 1

[tirf]

I have stored procdures returning a recordset
they basically look like this

create proc1
select col1, col2, col3
from tbl1


create proc2
select col1, col2
from tbl2


I have more than 30 of them

what I really want is how many records these procedures are returning;
I don't want to go and alter these procedures to have output parameter

can you suggest simple and efficient way?

Thanks

 

[gmmastros]

I don't think there is an efficient way of doing this. (notice I did not say simple). It doesn't make sense to return all the data just so you can ignore it and only use the counts. Instead, it would be better to create another stored procedure that gets the counts and returns them.

The simple way is to use @@RowCount, like this:

Declare @Proc1Count Int
Declare @Proc2Count Int

Exec Proc1

Select @Proc1Count = @@RowCount

Exec Proc2

Select @Proc2Count = @@RowCount

they basically look like this

Just curious... are there any where clauses? Is it always just one table? Do all of these tables have a primary key? If the answer to the above questions is YES, I think there may be a simple and fast method to get the table counts. Let me know.

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[tirf]

Thank you George

I tryed the same way, but I am not happy about it; when I run the query it retunrs all the resultsets and the count;
can we only get the count

Just curious... are there any where clauses? Is it always just one table? Do all of these tables have a primary key? If the answer to the above questions is YES, I think there may be a simple and fast method to get the table counts. Let me know.

- no there are no where clauses;
- It is always more than 1 table
- all tables have primary keys


Thanks

 

[gmmastros]

- no there are no where clauses;
- It is always more than 1 table
- all tables have primary keys

Always more than 1 table. This implies (to me) that the number of rows returned may not be the same as the number of rows in a table. That's a shame, because if it was, we could use that to our advantage.

You see, primary keys are made up of one or more columns. PK's must be unique for the column(s). PK's have a unique index. And... finally... we can get the number of rows in the index. so...

Select   Object_Name(id) As TableName,
         RowCnt As Rows 
From     sysindexes
Where    Status & 2048 = 2048
Order By TableName

The previous query will return a single row for each table that has a primary key and also return the number of rows in each table. Please note that this is an extremely efficient way to get row counts, but only for those tables that have a primary key.

** side note. Bit wise AND on the status column with value 2048 causes the results to be limited to primary keys.

If you cannot use the trick I just mentioned, then you have a couple options. You could rewrite the code to NOT use the stored procedures. This is likely to be the most efficient method because you can eliminate a lot of extra work.

Alternatively, you could modify the code I mentioned in my first response to put the data in to temp tables and then ignore it. Something like this:

Declare @Proc1Count Int
Declare @Proc2Count Int

Create Table #Temp1 (Col_1 Int, Col_2 VarChar(20))
Insert Into Table1 Exec Proc1
Select @Proc1Count = @@RowCount

Create Table #Temp2 (Col_1 Decimal(10,2), Col_2 bit)
Insert Into #Temp2 Exec Proc2
Select @Proc2Count = @@RowCount

The problem with this method is that is becomes dependent on the output structure of the stored procedures. For example, suppose you write all the code to get the counts from the 30 stored procedures. Everything works great. Then, 6 months from now, someone decides to add another column to the output of one of the procedures. Now the temp table you create does not match the output of the stored procedure and this code will break.

Bottom line, this code will be difficult to maintain.

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[tirf]

Thank you George, That was one good explanation; I probably use your first method

Thanks