Attempting to return more rows than what is available

Question

Below is an example of what my stored procedure looks like. One problem occurs when a user attempts to fetch a number of rows (@PageSize) more than what is currently available in the last page (@PageNumber), then the returned result is some how zero, instead of returning what is available even if less than the passed PageSize param.

This call returns result

exec [dbo].[CustomersPaginationProc] @LocationID ='0',@PageNumber=17999,@PageSize=10,@SortColumn=N'Name',@SortOrder=N'ASC'

while this call does not

exec [dbo].[CustomersPaginationProc] @LocationID ='0',@PageNumber=17999,@PageSize=20,@SortColumn=N'Name',@SortOrder=N'ASC'

Procedure detail:

    ALTER PROCEDURE [CustomersPaginationProc]
    -- Add the parameters for the stored procedure here
    @LocationID VARCHAR(50) = NULL
    @PageNumber INT = NULL, 
    @PageSize INT = NULL,
    @SortColumn NVARCHAR(20) = NULL,
    @SortOrder NVARCHAR(4) = NULL
 AS BEGIN
 SET NOCOUNT ON;

 WITH CTE_Results AS 
(
     SELECT 
          cs.LocationID
        , cs.Name
     FROM Customers cs with (nolock)  
     WHERE
        (@LocationID IS NULL OR cs.LocationID LIKE '%' + @LocationID + '%')
),
CTE_TotalRows AS 
(
     SELECT COUNT(*) AS MaxRows FROM CTE_Results
)
SELECT * FROM CTE_Results, CTE_TotalRows
     ORDER BY
            CASE WHEN (@SortColumn IS NOT NULL AND @SortColumn = 'LocationID' AND @SortOrder= 'ASC')
                        THEN CTE_Results.LocationID
            END ASC
      OFFSET @PageSize * (@PageNumber) ROWS
      FETCH NEXT @PageSize ROWS ONLY
      OPTION (RECOMPILE)
END

If there are 17999 pages when 10 rows are on a page, then there will be half that number of pages (~9000) if there are 20 rows on each page. Therefore passing in a PageNumber and PageSize to use in your calculation is not the best plan. — Mark Sinkinson, Mar 14 '18 at 16:15
Also, why are you doing a cross join between both your CTEs? — Mark Sinkinson, Mar 14 '18 at 16:18
@MarkSinkinson, what do you suggest as a better plan? In my scenario, the front end table allows a selection of different number of rows. — usefulBee, Mar 14 '18 at 16:19
This appears to be working as expected. If you request a page that is further than data that exists, I'd expect to get an empty page. Perhaps this approach will work better: https://stackoverflow.com/questions/109232/what-is-the-best-way-to-paginate-results-in-sql-server — EnterTheCode, Mar 14 '18 at 16:19
@usefulBee You should certainly not be allowing your users to choose both a page number and page size at the same time. For example, the SO reputation league allows for the choice of one or the other https://stackexchange.com/leagues/1/month/stackoverflow/ — Mark Sinkinson, Mar 14 '18 at 16:24
@MarkSinkinson, right I get your point now. I believe this is how it is done also in the app where the value of either ones is saved into cache, but at the end of the day both values are sent to the procedure. — usefulBee, Mar 14 '18 at 16:32
If you can't change your parameters, you will have to check if `@PageSize * @PageNumber > MaxRows` and in that case reduce `@PageNumber` to the last available one. Unfortunately you can't do that in a single statement. — EzLo, Mar 14 '18 at 16:33

S3S · Answer 1 · 2018-03-14T16:32:07.840

1

I don't get why you are doing the multiplication? Why the full join? This can be simplified, and works when the second parameter exceeds the number of available rows.

declare @startRows int = 95
declare @nextRows int = 10

--create 100 rows
;WITH
    E1(N) AS (select 1 from (values (1),(1),(1),(1),(1),(1),(1),(1),(1),(1))dt(n)),
    E2(N) AS (SELECT 1 FROM E1 a, E1 b), --10E+2 or 100 rows
    cteTally(N) AS 
    (
        SELECT  ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) FROM E2
    )

select N 
from cteTally
order by N
offset @startRows rows
fetch next @nextRows rows only

edited Mar 14 '18 at 16:32

answered Mar 14 '18 at 16:21

S3S

24,809
5
26
45

While technically it's a full join there just happens to be a single row in the CTE_TotalRows so it's the same as a full join with no where clause. http://rextester.com/BLRG57519 – S3S Mar 14 '18 at 16:31
Thank you. I believe I followed the code posted in the following article: https://dotnetdaily.net/featured/fast-sql-server-stored-procedure-with-filtering-sorting-and-paging – usefulBee Mar 14 '18 at 16:34

Attempting to return more rows than what is available

1 Answers1