sqlserver 按精度和取整方式格式化数值

 

IF OBJECT_ID('dbo.Fun_fomrataccnum') IS NOT NULL
DROP FUNCTION dbo.Fun_fomrataccnum
go
CREATE FUNCTION dbo.Fun_fomrataccnum (
 @num NUMERIC(38,4), --数值
 @roundtype int , --整方式类型 1舍去零头 2四舍五入 3直接进位
 @prec INT /* 精度 2-分 1角 0-元 -1 十元 -2百 -3千 -4万 */
) RETURNS NUMERIC(38,4)
AS
BEGIN
    
     IF @roundtype=1
       SET @num =ROUND(@num,@prec,1)
     ELSE IF @roundtype=2
       SET @num = Round(@num,@prec)
     ELSE 
      BEGIN
         IF (ROUND(@num,@prec,1)-ROUND(@num,@prec-1,1)>0)
           BEGIN
                SET @num =ROUND(@num,@prec,1)+POWER(CAST(0.1 AS FLOAT), @prec)
           END
         ELSE
           SET @num =ROUND(@num,@prec,1)
      END
      
     RETURN @num
  
END
 go