Rounding UP in SQL Server?

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.)

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 ) 

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