偏移获取下一步以获取所有行 [英] Offset Fetch Next to get all rows

问题描述

我在SQL Server 2012中有一个查询，该查询应根据我指定的页面大小及其所在的页面取回许多记录.看起来像这样:

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

代码非常简单.不过，我需要将其放入函数中以正确返回分页.但是，我可能还需要从该函数返回所有记录，因此我需要选择在没有任何OFFSET或FETCH的情况下调用该函数(大多数情况下，这是针对没有分页并且仅应为纯数据的报表) .我想不出一个好办法.

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.

推荐答案

您可以说:

@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;

当只需要所有行时，将NULL传递给两个参数.

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

这篇关于偏移获取下一步以获取所有行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！