Return the Row Count of stored procedures without returning the result set

Question

We have three stored procedures which return various columns and data which are included in one report.

Is it possible to return the row count of each of the three stored procedures without executing the return of any or all of the columns ? The columns are needed in the SSRS report.

We are just trying to ascertain if there is data being returned from the stored procedure up front.:

Declare @RD as Date ='2022-07-01'

exec [dbo].[usp_FMP_Query1] @RD --(Question: Does Data exist? If so, what is the row count?)
exec [dbo].[usp_FMP_Query2] @RD --(Question: Does Data exist? If so, what is the row count?)
exec [dbo].[usp_FMP_Query3] @RD --(Question: Does Data exist? If so, what is the row count?)

And if there are rows then we know whether or not to take appropriate action.

Answer 1

The best would to rewrite the procedures to take a @rowcount_only parameter. Inside the procedure, you would store the result set in a temp table, and then check if there any rows in the temp table. Keep in mind that this adds overhead, as the report query runs an extra time. Although, in some situations you may be lucky and take shortcuts with IF EXISTS. Or if you are building the query with dynamic SQL anyway, you can inject a TOP 1 for the row count, which could give faster execution.

If you cannot change these procedures, you can use INSERT EXEC:

CREATE TABLE #temp (....)
INSERT #temp
EXEC yourSP @RD

And then check if you have anything in the table.. Again, this means double execution. So you could almost just as well, execute the procedures from SSRS and then make decision based on that. I say almost, because with INSERT-EXEC you are at least keeping load of the network and SSRS.

Answer 2

Depending on the last statement in each stored proc you can use SELECT @@ROWCOUNT; after the call to the sp, it will return the row count of the last executed statement.

Answer 3

In SSRS you can use expression to calculate the query row count , SSRS use this syntax for the rowcount: =CountRows("DataSet1") Where DataSet1 is the name of your dataset in SSRS.