Home SELECT count from a large query, rows from a paged subset
Reply: 0

SELECT count from a large query, rows from a paged subset

user1968 Published in July 17, 2018, 8:00 am

I have a stored procedure that currently is doing something like this

 CREATE TABLE #results (col1 varchar(10), col2 varchar(10), col3 varchar(10), MaxRows BigInt)

 WITH Result AS (
     -- very complex query, lots of filters based on proc parameters
     SELECT Col1, Col2, Col3 
     FROM BaseTable
 , TempCount AS (
     SELECT Count(*) AS MaxRows FROM Result
 INSERT INTO #results (Col1, Col2, Col3, MaxRows)
 SELECT Col1, Col2, Col3
 FROM Result
 OFFSET @pagenumber * @pagesize  ROWS FETCH NEXT @pagesize ROWS ONLY;

 SELECT Col1, Col2, Col3 FROM #results;

 RETURN (SELECT TOP 1 MaxRows FROM #results);

The desire is to return up to @pagesize rows, and no more, but to have the procedure return value be the total number of rows in the complex select. @pagesize is a parameter to the stored procedure, as is @pagenumber.

To add some concreteness, the actual query selecting rows from an orders table for use in filling a grid on a web page. The customer very likely has hundreds or thousands of orders. Returning all those rows to the application would be wasteful, as the application can only display one page full at a time. But the application needs to know how many orders are available, so it can put the proper number of "next page" links at the bottom of the grid.

The only way we were able to think of was to use a temporary table. We insert the single page of results into the temporary table while also counting the full count of results into the MaxRows column. Then once it's in the temporary table, we can select the rows from the temporary table as the resultset, and the MaxRows column as the procedure return value.

Is there a way to do this without the temporary table? If we were returning the full resultset, then I know @@ROWCOUNT could be an option. But we want to only return one page worth of rows.

You need to login account before you can post.

About| Privacy statement| Terms of Service| Advertising| Contact us| Help| Sitemap|
Processed in 0.3323 second(s) , Gzip On .

© 2016 Powered by mzan.com design MATCHINFO