Rounding UP in SQL Server?

Question

I have written a paging system for sql server. and it works great but i am trying to return how many pages there are in total

Hence if there are 5 records and 2 records per page then the total is 3 pages

this is what i have

   SELECT @TotalPages = ( (SELECT COUNT(*) FROM #TempItems) / @RecsPerPage )

and my output parameter is defined like so

   @TotalPages AS INT OUT,

Now it sort of works :-) in my test there are 5 records and 2 records per page so the above select returns 2 but its wrong it should be 3

This is because its saying 5 / 2 = whole number 2... how do i round up...?

I tired ceiling but couldn't get it to work..

Any ideas?

Thanks in advance

Answer 1

Did you try Casting either the numerator and the denominator as float and then using Cieling?

Integer operations always give integers. Try the following -

SELECT @TotalPages = CEILING((SELECT cast(COUNT(*) as float) FROM #TempItems) / @RecsPerPage )