【求高手解决】sql自定义函数，取有效数字的问题
希望得到的结果如下：

select dbo.fn_test('123456',4)
--123500

select dbo.fn_test('1.23456',4)
--1.235

select dbo.fn_test('0.00123456',4)
--0.0012

在CSDN上转载Haiwer大大的程序如下,不能得到上述结果，求解决:
alter function fn_test(
@s varchar(100),
@i int
)
returns float
as
begin
  declare @f float
  set @f=cast(@s as float)
  declare @d int
  set @d=0
  declare @n int 
  if @f>0 
  set @n=1
  else 
  set @n=-1
  set @f=abs(@f)
  while @f>1
  begin
  set @d=@d+1
  set @f=@f/10
  end

  while @f<0.1
  begin
  set @d=@d-1
  set @f=@f*10
  end

  return @n*ROUND(@f,@i)*power(cast(10 as float),@d)
end
go


------解决方案--------------------
或者要想得到 0.0012
原函数不变，调用：select dbo.fn_test('0.00123456',2)

------解决方案--------------------
试试

SQL code

IF OBJECT_ID('fn_test') IS NOT NULL DROP FUNCTION dbo.fn_test
go
 
CREATE FUNCTION fn_test (@s VARCHAR(100) , @i INT)
RETURNS FLOAT
AS 
BEGIN
  DECLARE @f FLOAT
  SET @f = CAST(@s AS FLOAT)
  DECLARE @d INT
  SET @d = 0
  DECLARE @n INT  
  IF @f > 0 
    SET @n = 1
  ELSE 
    SET @n = -1
  SET @f = ABS(@f)
  WHILE @f > 1 
    BEGIN
      SET @d = @d + 1
      SET @f = @f / 10
    END

  WHILE @f < 0.1 
    BEGIN
      SET @d = @d - 1
      SET @f = @f * 10
      SET @i = @i - 1
    END

  RETURN @n*ROUND(@f,@i)*POWER(CAST(10 AS FLOAT),@d)
END
GO