11

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.

CrystalBlue
  • 1,793
  • 5
  • 18
  • 27
  • Why not just call it with a `@PageSize` big enough to return all records? – Lieven Keersmaekers Sep 11 '12 at 18:39
  • 1
    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. – CrystalBlue Sep 11 '12 at 18:47
  • 2
    Upper bound of INT or BIGINT should be sufficient, depending... – Aaron Bertrand Sep 11 '12 at 18:49
  • @CrystalBlue - 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. – Lieven Keersmaekers Sep 11 '12 at 19:11
  • 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? – CrystalBlue Sep 12 '12 at 16:40

1 Answers1

13

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.

Aaron Bertrand
  • 272,866
  • 37
  • 466
  • 490