SQL Server存储过程执行报Error converting varchar to numeric错求助

执行存储过程触发的报错信息如下：

Msg 8114, Level 16, State 5, Procedure spMobileOperationsNew, Line 0 [Batch Start Line 0]
Error converting data type varchar to numeric.

问题根因 #

• 存储过程内所有数值类型的参数（包括int类型的@disYear，以及所有decimal类型的@territoryTarget、@terDiscRate、@maxDiscRate、@minBill、@allocateBudget、@actualValue、@actDiscValue、@finalBudget）的默认值被错误设置为空字符串''
• 本次执行仅传入了前4个参数，后续未传的参数会触发使用默认值，SQL Server在参数绑定阶段尝试将空字符串转换为对应数值类型失败，直接触发报错

修复方案 #

将所有数值类型参数的默认值修改为NULL或者符合业务要求的合法数值（比如0）即可，修改后的存储过程参考如下：

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE [dbo].[spMobileOperationsNew]
    @action AS nvarchar(50),
    @territorycode nvarchar(10) = '',
    @disYear  AS int = NULL, -- 原默认值为''，修改为NULL或0
    @disMonth AS nvarchar(10) = '',
    @territoryTarget AS decimal(18, 2) = NULL, -- 原默认值为''，修改为NULL或0
    @terDiscRate AS decimal(5, 2) = NULL, -- 原默认值为''，修改为NULL或0
    @maxDiscRate AS decimal(5, 2) = NULL, -- 原默认值为''，修改为NULL或0
    @minBill AS decimal(18, 2) = NULL, -- 原默认值为''，修改为NULL或0
    @allocateBudget AS decimal(18, 2) = NULL, -- 原默认值为''，修改为NULL或0
    @actualValue AS decimal(18, 2) = NULL, -- 原默认值为''，修改为NULL或0
    @actDiscValue AS decimal(18, 2) = NULL, -- 原默认值为''，修改为NULL或0
    @finalBudget AS decimal(18, 2) = NULL -- 原默认值为''，修改为NULL或0
AS
BEGIN
    SET NOCOUNT ON;

    IF (@action = 'Get_TerritoryWiseTradeDiscount')
    BEGIN
        PRINT ('get territory')

        SELECT
            LTRIM(RTRIM(tt.[TerCode])) AS [TerCode], 
            tt.[Year], 
            (CASE tt.[Month] 
                 WHEN 'January' THEN 1 
                 WHEN 'February' THEN 2 
                 WHEN 'March' THEN 3 
                 WHEN 'April' THEN 4 
                 WHEN 'May' THEN 5 
                 WHEN 'June' THEN 6 
                 WHEN 'July' THEN 7 
                 WHEN 'August' THEN 8 
                 WHEN 'September' THEN 9
                 WHEN 'October' THEN 10 
                 WHEN 'November' THEN 11 
                 WHEN 'December' THEN 12 
             END) AS [Month], 
            tt.[MaxDiscRt], tt.[MinBillVal], tt.[TerTgt], 
            tt.[TerDiscRt], tt.[AllDiscBud], tt.[ActVal], 
            tt.[ActDiscVal], tt.[FinalDiscBudget]
        FROM
            TerritoryWiseTradeDiscount AS tt
        WHERE 
            tt.[TerCode] = @territorycode 
            AND tt.[Year] = @disYear 
            AND tt.[Month] = @disMonth
    END
END

修改后重新创建存储过程，再执行原调用语句就不会触发类型转换报错。如果业务逻辑中需要判断这些参数是否传值，使用NULL作为默认值也更方便做后续判断。

内容的提问来源于stack exchange，提问作者The Ark D