【求高手解决】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_testgo CREATE FUNCTION fn_test (@s VARCHAR(100) , @i INT)RETURNS FLOATAS 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)ENDGO