paginationsql-server-2012

Offset Fetch Next to get all rows


I have a query in SQL Server 2012 that should get back a number of records based on the page size I specify and the page it is on. It looks like this:


SELECT LocID, LocName
FROM Locations
ORDER BY LocName OFFSET @PageNum ROWS
FETCH NEXT @PageSize ROWS ONLY

The code is pretty simple. What I need to do, though, is put that into a function to return the paging correctly. However, I could also be needing all records back from this function, so I need to have the option of calling the function without any OFFSET or FETCH (mostly, this is for a report that has no paging and should only be the straight data). I can't think of a good way to do this.


Solution

  • You could say:

    @PageNum  INT,
    @PageSize INT
    
    ...
    
    SELECT @PageSize = COALESCE(@PageSize, 2000000000);   
    -- 2 billion should be enough?
    
    ... OFFSET (COALESCE(@PageNum, 1)-1)*@PageSize ROWS
    FETCH NEXT @PageSize ROWS ONLY;
    

    When you just want all rows, pass in NULL to both parameters.