Offset Fetch Next to get all rows

Question

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.

Boutte

Why not just call it with a @PageSize big enough to return all records? – Remove

The problem is, I don't know how big the record set is going to be until after I've run it. I had another question about something similar to that problem as well. – Boutte

Upper bound of INT or BIGINT should be sufficient, depending... – Distill

@Boutte - I agree with Aaron. Your server/network/client will definitely timeout before you come anywhere near the amount of records that can be returned by specifying the upper bound of an int. – Remove

I understand that this is a valid tactic right now (I've set the page size to around a million records for now) and that solves my dilemma. But is that the answer to my question: there is no way of doing what I want to do? – Boutte

Answer 1

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.

Distill

This is what I've been suggested to do by other people for now, so this seems like the most valid answer. Thanks! – Boutte

When I pass NULL to both parameters, I get the following error: The number of rows provided for a TOP or FETCH clauses row count parameter must be an integer. – Crossland

@MeyssamToluie So stop passing NULL or use COALESCE to establish defaults. – Distill