Nice function.

Tip 1: Instead of the CASE to get rid of negative values, use ABS.

Tip 2: Add a CASE to work around the domain error you'd get from LOG10(0).

Tip 3: Stop being lazy and use decimal instead of float. Because float can't represent many values exactly, you run the risk of errors. For instance:

SELECT dbo.fix(1.15)   -- Returns 1.1 instead of 1.2

alter function dbo.fix(@num numeric(36,18), @digits int) returns numeric(36,18) as

begin

declare @res float

select @res = case when @num = 0 then 0

else round(@num,@digits-1-floor(log10(abs(@num)))) end

return (@res)

Very sweet!

But why the extra overhead of declaring and setting a variable?  Why not just return the calc'd value?

...
BEGIN
RETURN CASE WHEN ...
END

Nice to see someone using LOGs. But I would trust your results more if you displayed them, instead of using the WHERE expected_result <> actual_result.

The problem I found was that you don't get exact representation when using float. Frankly, I have never used float before, so maybe I am doing something incorrectly, but note the example below.

declare   @num float
           , @res float
           , @digits int
SET @num = 1.23456678
SET @digits = 4
select @res = case when @num > 0
                      then round(@num,@digits-1-floor(log10(@num)))
                      else round(@num,@digits-1-floor(log10(-@num)))
                    end
select @num as ORIGINAL_NUMBER, @res AS RESULT 
       , @digits as NBR_SIG_DIGITS

RESULT IS:
ORIGINAL_NUMBER         RESULT                  NBR_SIG_DIGITS
1.23456678             1.2349999999999999            4

Probably not what you really want (I assume you want 1.235 ??)

Hi Skip,

>>The problem I found was that you don't get exact representation when using float<<

Of course you don't - check Books Online. Float is an approximate-number data type. For most numbers, it can only store a (close) approximization.

Internally, float uses base-2 representation. As a result, only numbers that can be written as x + (y / EXP(2, z)) where x and y are integers and z is a non-negative integer can be represented exactly in that representation. Just like our common base-10 representation can only represent an approximation of 1/3, float can only represent an approximation of 1.235.

That's one of the reasons why I wrote that the function should use decimal instead of float in my previous reply (though I now see that I forgot to change the datatype for the internal variable).