Menu
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.
794
To query more than 1000 rows, there are two ways to go about this. Use the '$offset=' parameter by setting it to 1000 increments which will allow you to page through the entire dataset 1000 rows at a time. Another way is to use the '$limit=' parameter which will set a limit on how much you query from a dataset.
All replies. select <column list you want> from <your table name> order by ProductName offset 100 rows fetch next 100 rows only; That will skip the first 100 rows (in order by ProductName) and return the next 100 rows.
The result offset clause provides a way to skip the N first rows in a result set before starting to return any rows. The fetch first clause, which can be combined with the result offset clause if desired, limits the number of rows returned in the result set.
OFFSET can only be used with ORDER BY clause. It cannot be used on its own. OFFSET value must be greater than or equal to zero. It cannot be negative, else return error.
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.
97
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
