In the following scalar function,
a fractional value between 0.1 and 0.4 is set to 0.5
a fractional value of 0.6 and above is rounded up to the next integer
ALTER FUNCTION [dbo].[BHERound]
( @val float ) RETURNS float AS BEGIN -- Declare the return variable here declare @frac float --fractional part of the value declare @newfrac float Set @frac = @val - round(@val,0,1) set @newfrac = 0 If @frac >= 0.1 and @frac <=0.4 set @newfrac = 0.5 else if @frac >= 0.6 set @newfrac = 1 else if @frac > 0.4 and @frac < 0.6 set @newfrac = 0.5 return round(@val,0,1) + @newfrac END
this works fine in SMSS e.g.
select dbo.BHERound(1.6) , gives the answer 2, as expected
however the following query
select *,dbo.BHERound(RawBHE) as BHE from tblTESTRawBHE where PosRef='FCILLC002220'