【sql】将 float 转换为数据类型 numeric 时出现算术溢出错误

decimal(18,8)小数点前面最多有10位

14321273753.14745900小数点前面有11位，超出10位了就会报这个错

参考

https://blog.csdn.net/liuxufeiyang000/article/details/50393521

修改为decimal(38,8)，不报错了

select [dbo].[DiseaseData](303,3)

 

USE [AirYaYC]
GO

/****** Object:  UserDefinedFunction [dbo].[DiseaseData]    Script Date: 2018-12-10 10:12:30 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

-- =============================================
-- Author:        <pyy>
-- ALTER date: <2018.9.19>
-- Description:    <疾病率值>
-- =============================================

CREATE function [dbo].[DiseaseData](@val decimal(18,8),@style int)
returns decimal(38,8)
as
begin

    declare @rate decimal(38,8)

    set @rate = 0.0

    if (@style = 1 )--and @val<=44
        set @rate =(Exp(1.09*0.01 * (@val - 25))-1)*10.64 * 5
    --else if (@style = 1 and @val>44)
        --set @rate = 9999999999

    else if (@style = 2 )--and @val<=55    
        set @rate = (Exp(0.68*0.01 * (@val - 25))-1)*10.64 * 11.5
    --else if (@style = 2 and @val>55)
        --set @rate = 9999999999

    else if (@style = 3 )--and @val<=28    
        set @rate = (Exp(7.9*0.01 * (@val - 25))-1)*10.64 * 0.39
    --else if (@style = 3 and @val>28)
        --set @rate = 9999999999

    else if (@style = 4 )--and @val<=58
        set @rate = (Exp(0.56*0.01 * (@val - 25))-1)*10.64 * 56.1
    --else if (@style = 4 and @val>58)
        --set @rate = 9999999999

    else if (@style = 5 )--and @val<=59    
        set @rate = (Exp(0.49*0.01 * (@val - 25))-1)*10.64 * 371.71
    --else if (@style = 5 and @val>59)
        --set @rate = 9999999999

    else if (@style = 6 )--and @val<=57
        set @rate = (Exp(0.56*0.01 * (@val - 25))-1)*10.64 * 135.74
    --else if (@style = 6 and @val>57)
        --set @rate = 9999999999

    else if (@style = 7 )--and @val<=41
        set @rate = (Exp(1.43*0.01 * (@val - 25))-1)*10.64 * 0.4186
    --else if (@style = 7 and @val>41)
        --set @rate = 9999999999

    else if (@style = 8 )--and @val<=69    
        set @rate = (Exp(0.53*0.01 * (@val - 25))-1)*10.64 * 0.64375
    --else if (@style = 8 and @val>69)
        --set @rate = 9999999999

   --if (@rate >= 9999999999)
   --set @rate = 9999999999
    return @rate
end