SQL Server procedure design to get results and results count in a single call

Question

I have a requirement where I need to pull the top 100 of the data result set and also the count of the records of the total data which satisfy my filtering condition.

Thanks, Manoj.

Answer 1

You can use an output parameter to return the total number of records and also a select statement that returns a result set.

For example:

create procedure test
   @totalCount int output
 as begin
     select * from something -- your select statement goes here
     select @totalCount = 'write your statement that counts the records.'
 end

If you need to call it from code, here's a Java example to illustrate how you would make the call:

public void test(Connection con) throws SQLException{
    CallableStatement cs = con.prepareCall("test");
    //you can set more parameters if you need to, i.e.: cs.setInt(0,id);

    //you need to register your output parameter. If you are referencing it by name, it should match with the name given in the stored procedure.
    cs.registerOutputParameter("totalCount", Types.INTEGER);
    ResultSet rs = cs.executeQuery();
    while(rs.next()){
       //you can save the data into a data structure; a list for example.
       //Or just print the records.
    }
    //here's how you can get the total count
    int total = cs.getInt("totalCount");
}

This method is just an example to show you how to make the call. Don't write production code like this!!!

Answer 2

One of my answers is similar to A2H, but if your "count" is directly related to the query of your select, you can avoid a "double hit", but placing the results in the #temp table.

However, you can also use the @@ROWCOUNT trick as well. Which is a new answer, IMHO.

CREATE PROCEDURE dbo.SelectAndCountExample
   @TotalCount int output
 as 

BEGIN

IF OBJECT_ID('tempdb..#Holder') IS NOT NULL
begin
    drop table #Holder
end


CREATE TABLE #Holder
(SurrogateKey INT, SomeValue VARCHAR(64) )


/* simulate your insert */
INSERT INTO #HOLDER (SurrogateKey , SomeValue) 
select 101 , 'A' union all select 102, 'B'  union all select 103, 'C'  union all select 104, 'D' 

/* optional stuff */   
/* CREATE CLUSTERED INDEX IDX_TempHolder_ID ON #Holder (ID) */
/* CREATE INDEX IDX_TempHolder_ID ON #Holder (ID) */

Select @TotalCount = count(*) From #Holder

Select SurrogateKey , SomeValue from #HOLDER


IF OBJECT_ID('tempdb..#Holder') IS NOT NULL
begin
    drop table #Holder
end


END

GO


declare @MyVariable int
EXEC dbo.SelectAndCountExample @MyVariable output
Select @MyVariable as '@MyVariable Value'



GO

and

CREATE PROCEDURE dbo.SelectAndCountExampleUsingRowCount
   @TotalCount int output
 as 

BEGIN


select 101 , 'A' union all select 102, 'B'  union all select 103, 'C'  union all select 104, 'D' 

Select @TotalCount = @@ROWCOUNT


IF OBJECT_ID('tempdb..#Holder') IS NOT NULL
begin
    drop table #Holder
end


END

GO


declare @MyVariable int
EXEC dbo.SelectAndCountExampleUsingRowCount @MyVariable output
Select @MyVariable as '@MyVariable Value'

Answer 3

It is not uncommon to need to page through results while also showing the total number of possible pages of results, and there are likely quite a few questions on here already about this. Unfortunately, there is no built-in mechanism to handle this. That leaves you with only a few options:

1. Run the query twice: once to get the total number of rows, and then again to get the subset / page of results that is needed. The downside here is that you are running it twice. (this is the basis of @A2H's answer)

2. Run the query once, dumping the results into a local temporary table and capturing the number of rows that went into the temporary table using @@ROWCOUNT. Then, SELECT the subset / page of results that is needed from the temporary table. The downside here is that you are taking a hit on IO (tempdb datafile and transaction log), though that might be possible to be improved by using In-Memory OLTP. (similar to @granadaCoder's answer, but that answer doesn't follow-through on the subset of rows requirement)

3. Build it into the app-code:

   • declare a counter to keep track of the total number of rows
   • run the full query
   • call SqlDataReader.Read() (assuming you are using .NET) -- without actually retrieving any of the data -- for the number of rows to skip, then read the rows for the subset / page of results that is needed, and finally call SqlDataReader.Read() -- again, without actually retrieving any of the data -- until there is nothing left to read. Increment the row counter in each of the 3 sections.

   Please see the following answer of mine, also on StackOverflow, that provides more details (and variations) on this approach:

   TSQL: Is there a way to limit the rows returned and count the total that would have been returned without the limit (without adding it to every row)?