CODEWITHSUNDEEP
sql-serverstored-procedurespagination
usefulBee
usefulBee

Reputation: 9702

Attempting to return more rows than what is available

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

Upvotes: 0

Views: 49

Answers (1)

S3S
S3S

Reputation: 25132

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

Upvotes: 1

Related Questions