sqlの四捨六入5対の関数(1)
1457 ワード
--drop function MyRound
create function MyRound(@num numeric(20,10),@i int)
--@i
--@num
returns varchar(20)
as
begin
declare @numtemp numeric(20,10)
declare @result varchar(20)
set @numtemp=abs(@num)*power(10,@i+1)
select @numtemp=(case when floor(@numtemp)-floor(@numtemp/10)*10=5
then (case when @numtemp-floor(@numtemp)=0
then (case when cast(floor(@numtemp/10) as int)%2=0
then floor(@numtemp/10)/power(10,@i) -- 5， ，
else round(@numtemp/power(10,@i+1),@i)-- 5， ，
end)
else round(@numtemp/power(10,@i+1),@i) -- 5,
end)
else round(@numtemp/power(10,@i+1),@i) -- 5
end)
set @result=(case when @num> 0 then str(@numtemp,20,@i) else str(0-@numtemp,20,@i) end)
return @result
end