A customer recently had a problem where their app couldn't access certain rows in a database. Attempting to select these rows yielded a SQL Server error: An error occurred while executing batch. Error message is: Arithmetic Overflow. Further investigation revealed that the error was caused by attempting to select the value in a column of type float. Casting the column to a varchar yielded the value #1.INF, which some research revealed was a value representing positive infinity. Since I was tasked with the job of cleansing these infinity values from the database and also ensuring that all logic accessing these columns would both attempt to prevent infinity from being inserted and also handle infinity if it did reappear in a row, I decided to encapsulate the logic in a scalar user-defined function, which is sufficiently generic to share with anyone else dealing with this issue, so without further ado, here it is:

create function dbo.float_cleanse
(
  @float float(53)
)
returns float(53)
as
begin
  declare @float_max float(53)
  -- from SQL Server BOL
  set @float_max = 1.79E+308

  return case
    -- positive infinity
    when @float > @float_max
      then @float_max
    -- negative infinity
    when @float < -@float_max
      then -@float_max
    -- a float!
    when @float between -@float_max and @float_max
      then @float
    -- NaN
    else 0
  end
end