Geek asks and answers

Sample Offset Next to get all rows

I have a query in SQL Server 2012 that should return a few records depending on the size of the page that I specify and the page on which it is included. 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. However, I need to do this in the function in order to correctly return the paging. However, I may also need all the entries from this function, so I need to be able to call the function without any OFFSET or FETCH (basically this is for a report that has no swap and should only be direct), I can't think of , how to do it.

+5
source share
1 answer

You can 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 need all the lines, go to NULL for both parameters.

+8

More articles:


All Articles