SQL自定义舍入修改小数点后数值及varchar参数转换报错问题咨询

问题解答 #

1. 能不能用varchar作为入参？
   可以但非常不推荐，你当前的报错原因就是varchar类型在和数值做大小比较时，SQL会触发隐式类型转换，只要传入的字符串不符合数值格式、存在多余字符/空格，就会直接抛出转换错误，同时隐式转换也会带来额外的性能损耗。

2. 改用real类型作为入参还能使用substring吗？
   不能，substring是字符串专属处理函数，无法直接作用于数值类型，就算你把real手动转成字符串再处理，也会遇到real本身的近似值精度问题：比如0.29存储时可能变成0.2899999999，转字符串后截取结果完全不符合预期，real属于近似数值类型，本身就不适合用于精确舍入场景。

正确实现方案 #

推荐入参使用精确数值类型DECIMAL，通过数值运算直接提取整数、小数部分，完全避免字符串处理带来的各种问题，示例代码如下：

CREATE FUNCTION dbo.Myfunction
(
    @Liczba DECIMAL(18,2) -- 可根据业务场景调整精度范围
)
RETURNS DECIMAL(18,2)
AS
BEGIN
    DECLARE @IntegerPart INT = FLOOR(@Liczba) -- 提取整数部分
    DECLARE @DecimalPart INT = ROUND((@Liczba - @IntegerPart) * 100, 0) -- 提取小数点后两位数值

    -- 0<=数值<7的分支
    IF @Liczba >=0 AND @Liczba <7
    BEGIN
        IF @DecimalPart BETWEEN 0 AND 24 RETURN @IntegerPart + 0.19
        IF @DecimalPart BETWEEN 25 AND 34 RETURN @IntegerPart + 0.29
        IF @DecimalPart BETWEEN 35 AND 44 RETURN @IntegerPart + 0.39
        IF @DecimalPart BETWEEN 45 AND 54 RETURN @IntegerPart + 0.49
        IF @DecimalPart BETWEEN 55 AND 64 RETURN @IntegerPart + 0.59
        IF @DecimalPart BETWEEN 65 AND 74 RETURN @IntegerPart + 0.69
        IF @DecimalPart BETWEEN 75 AND 88 RETURN @IntegerPart + 0.79
        IF @DecimalPart BETWEEN 89 AND 99 RETURN @IntegerPart + 0.99
    END

    -- 7<=数值<10的分支
    IF @Liczba >=7 AND @Liczba <10
    BEGIN
        IF @DecimalPart BETWEEN 0 AND 38 RETURN @IntegerPart + 0.29
        IF @DecimalPart BETWEEN 39 AND 63 RETURN @IntegerPart + 0.49
        IF @DecimalPart BETWEEN 64 AND 88 RETURN @IntegerPart + 0.79
        IF @DecimalPart BETWEEN 89 AND 99 RETURN @IntegerPart + 0.99
    END

    -- 10<=数值<15的分支
    IF @Liczba >=10 AND @Liczba <15
    BEGIN
        IF @DecimalPart BETWEEN 0 AND 38 RETURN @IntegerPart + 0.29
        IF @DecimalPart BETWEEN 39 AND 63 RETURN @IntegerPart + 0.49
        IF @DecimalPart BETWEEN 64 AND 88 RETURN @IntegerPart + 0.79
        IF @DecimalPart BETWEEN 89 AND 99 RETURN @IntegerPart + 0.99
    END

    -- 15<=数值<30的分支
    IF @Liczba >=15 AND @Liczba <30
    BEGIN
        IF @DecimalPart BETWEEN 0 AND 49 RETURN @IntegerPart + 0.49
        IF @DecimalPart BETWEEN 50 AND 99 RETURN @IntegerPart + 0.99
    END

    -- >=30的分支，原代码重复逻辑已合并，功能完全一致
    IF @Liczba >=30
    BEGIN
        RETURN @IntegerPart + 0.99
    END

    RETURN @Liczba
END

内容的提问来源于stack exchange，提问作者Simonsoft177