calculate number of pages of records in select statement

SO I am trying to figure out how many pages I will need to display my records, basically want a page count.

I was going to do something like this:

SELECT COUNT(*) % 50 
FROM Customers 
WHERE ManufacturerID=@ManufacturerID 
AND ModifiedOn > @tStamp

SO for that statement I would want to get back a 2 for 99 records, 2 for 100, and 3 for 101

Will this work? Is this a bad practice? Seems a little wonky on me te开发者_Python百科sting some values.

EDIT FOR CLARIFICATION: I don't want to get the paged records, just the total number of pages

To calculate the number of pages, just take the ceiling of the number of records over the page size:

SELECT CEILING(COUNT(*) / @PageSize) FROM ...

with @PageSize being 50 in your case. Or, since your application probably knows the desired number of records to show per page, just write a query that returns the COUNT(*) from your table, and do the calculation in your code. For example:

var totalPages = Math.ceil(recordCount / pageSize);

When you get to the point where you want to select the records that belong to a page, I do it like this (just in case you were curious):

WITH Paged AS
(
    SELECT
        ROW_NUMBER() OVER(ORDER BY SortColumn) AS [RowNum]
        ,* -- because we're going to choose which columns we need later
    FROM
        [MyTable]
)
SELECT 
     [Column1]
    ,[Column2]
    ,etc...
FROM 
    Paged
WHERE 
    [RowNum] BETWEEN (@PageNumber - 1) * @PageSize + 1 AND @PageNumber * @PageSize
ORDER BY 
    [SortColumn]

You have to pass in @PageNumber and @PageSize from your application.

If you use

ROW_NUMBER() OVER ( {ORDER statement} ) AS ROWNUMBER

in your paged SELECT statement, you can then add

COUNT(*) OVER() AS TOTALCOUNT

to get the total number of records found by the query

I think, the basic question of getting total number of pages still remained un-answered.

By using a simple logic, I think it can be achieved in SQL Query:

TotalPages = (Count(*) OVER() + @NumberOfItems - 1)/@NumberOfItems

where "@NumberOfItems" is number of items to be displayed in a Page. Hope this helps someone.