Rounding UP in SQL Server?

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 开发者_如何学Pythonits 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

---

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 ) 

---

You can use integer arithmetics for that:

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

(I figured out that formula many, many years ago (before there was an internet where you could ask thinks like this), and have used it many times.)

---

SQL Server will always give an integer result when you divide two integers.

You can add a ".0" to the end of actual hard-coded values, or multiply by "1.0" to convert a field value. If you want to round up, then the best way I know is to add 0.5, then call the normal ROUND function. That works because ROUND_DOWN(number + .5) is always the same as ROUND_UP(number).

You can also manually cast a field to a float, as others have pointed out.

But note that

round(1.0 / 2.0,0)

will return a floating point result, whereas

round(cast(1 as float) / cast(2 as float),0)

will give an integer result. So use the one that works best for you.

All of these are illustrated in the below example:

SELECT
  ROUND(5/2,0) AS INTEGER_EXAMPLE
 ,ROUND((5.0 / 2.0),0) AS FLOATING_POINT_EXAMPLE
 ,ROUND(CAST(5 AS FLOAT) / CAST(2 AS FLOAT),0) AS CASTING_EXAMPLE
 ,ROUND((5 * 1.0) / (2 * 1.0),0) AS CONVERTED_TO_FP_EXAMPLE
 ,ROUND(((1 * 1.0) / (4 * 1.0)) + (1.0 / 2.0),0) AS ROUNDED_UP_EXAMPLE

Here is a function that will do round-ups for you, along with a SELECT to show you the results it gives.

create function roundup(@n float) returns float
as
begin
  --Special case: if they give a whole number, just return it
  if @n = ROUND(@n,0)
    return @n;
    
  --otherwise, add half and then round it down
  declare @result float = @n;
  declare @half float = (1.0 / 2.0);
  set @result = round(@n + @half,0);
  
  return @result;
end
GO
select
  dbo.roundup(0.0) as example_0
 ,dbo.roundup(0.3) as example_pt_three
 ,dbo.roundup(0.5) as example_pt_five
 ,dbo.roundup(0.9) as example_pt_nine

---

Integer math doesn't round, it truncates. Change @RecsPerPage to be a float instead of int, and then use ceil in your sql statement.

---

Try

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

---

Here some similar code. The CAST functions keep SQL from automatic rounding. Adjust the 0.00 to the precision you want.

declare @C int
declare @D int

SET @C = 5
SET @D = 2

DECLARE @Answer FLOAT

SELECT @Answer = CAST(@C as FLOAT) / CAST(@D as FLOAT)

SET @Answer = CASE WHEN @Answer - ROUND(@Answer, 0) > 0.00 THEN ROUND(@Answer, 0) + 1
                   ELSE ROUND(@Answer, 0) END

PRINT @Answer

edit - correct rounding param. to 0

---

DECLARE @Count int
SELECT @Count = COUNT(*) FROM #TempItems
SELECT @TotalPages = @Count / @RecsPerPage 
IF @Count % @RecsPerPage > 0 SET @TotalPages = @TotalPages +1

---

Keep your original formula, but you need to check to see if the remainder is > 0, and if so just add 1 to your result.