CODEWITHSUNDEEP
sql-server
SHD
SHD

Reputation: 409

Pagination based on parameters (OFFSET FETCH NEXT)

How I display All data when parameters are null,

DECLARE @PageSize INT = 5
        ,@PageNo  INT = 2

SELECT *
FROM [MyTable]
ORDER BY [NO]
OFFSET @PageSize * (@PageNo - 1) ROWS
FETCH NEXT @PageSize ROWS ONLY

Upvotes: 3

Views: 4289

Answers (1)

TriV
TriV

Reputation: 5148

You could try this

SET @PageSize = ISNULL(@PageSize, 2147483647) -- max int
SET @PageNo = ISNULL(@PageNo, 1)

SELECT *
FROM [MyTable]
ORDER BY [NO]
OFFSET @PageSize * (@PageNo - 1) ROWS
FETCH NEXT @PageSize ROWS ONLY

Upvotes: 6

Related Questions