Better way for Getting Total Count along with Paging in SQL Server 2012
If we're allowed to change the contract, you can have:
SELECT CSTNO, CSTABBR,COUNT(*) OVER () as TotalCountFROM DBATABCWHERE CSTABBR LIKE 'A%'ORDER BY CSTNOOFFSET ( @OffSetRowNo-1 ) * @FetchRowNo ROWSFETCH NEXT @FetchRowNo ROWS ONLYAnd 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.
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.