在SQL Server 2012中获取总计数和分页的更好方法

I have requirement to get the total count of records along with paging. At present I am doing it as listed below in SQL Server 2012. This needs a separate query for getting count. Is there any improved way in SQL Server 2012?

我要求获取记录的总数和分页。目前我在SQL Server 2012中列出如下所示。这需要一个单独的查询来获取计数。 SQL Server 2012中是否有任何改进的方法？

ALTER PROCEDURE dbo.tpGetPageRecords
(
    @OffSetRowNo INT,     
    @FetchRowNo INT,
    @TotalCount INT OUT
) 
AS 

SELECT CSTNO, CSTABBR 
FROM DBATABC
WHERE CSTABBR LIKE 'A%'
ORDER BY CSTNO
OFFSET ( @OffSetRowNo-1 ) * @FetchRowNo ROWS
FETCH NEXT @FetchRowNo ROWS ONLY

SET @TotalCount = 
(SELECT COUNT(*)
FROM DBATABC
WHERE CSTABBR LIKE 'A%')


GO

1 个解决方案

#1

If we're allowed to change the contract, you can have:

如果我们允许更改合同，您可以：

SELECT CSTNO, CSTABBR,COUNT(*) OVER () as TotalCount
FROM DBATABC
WHERE CSTABBR LIKE 'A%'
ORDER BY CSTNO
OFFSET ( @OffSetRowNo-1 ) * @FetchRowNo ROWS
FETCH NEXT @FetchRowNo ROWS ONLY

And now the total will be available as a separate column in the result set. Unfortunately, there's no way to assign this value to a variable in this same statement, so we can no longer provide it as an OUT parameter.

现在，总数将作为结果集中的单独列提供。不幸的是，没有办法将此值赋给同一语句中的变量，因此我们不能再将其作为OUT参数提供。

This uses the OVER clause (available since 2005) to allow an aggregate to be computed over the entire (unlimited) result set and without requiring GROUPing.

这使用OVER子句（自2005年起可用）允许在整个（无限制）结果集上计算聚合，而不需要GROUPing。

#1