SQL Server存储过程执行报错：varchar转numeric类型转换失败

从你提供的错误信息、存储过程和执行语句来看，这个错误主要由两个核心问题导致，下面逐一分析并给出修复方案：

问题1：生成ReceiptNo时的子串截取错误 #

在存储过程的插入逻辑中，你用这段代码生成新的ReceiptNo：

@INVOICENO + '/REC/' + (SELECT CONVERT(VARCHAR, Isnull( Max(CONVERT(NUMERIC, Substring( receiptno, 21, 50 ))) + 1, 1)) FROM invoicepayment_lko WHERE invoiceid = @invoiceid)

而你的执行语句中@InvoiceNo是'LP/0819/0000183'，拼接后的初始ReceiptNo会是LP/0819/0000183/REC/1，这个字符串总长度只有20位。当你用Substring(receiptno,21,50)时，会返回空字符串，空字符串无法转换为numeric类型，这直接触发了Msg 8114错误。

修复方案： #

调整子串截取的起始位置，或者改用更可靠的方式生成自增ReceiptNo：

1. 动态计算前缀长度，确定截取起始位：

DECLARE @prefixLen INT = LEN(@INVOICENO + '/REC/')
@INVOICENO + '/REC/' + (SELECT CONVERT(VARCHAR, Isnull( Max(CONVERT(NUMERIC, Substring( receiptno, @prefixLen + 1, 50 ))) + 1, 1)) FROM invoicepayment_lko WHERE invoiceid = @invoiceid)

2. 更推荐的方式是单独维护一个针对每个InvoiceID的计数器表，彻底避免字符串截取带来的转换风险。

问题2：执行语句中参数类型不匹配 #

你的执行语句里，部分数值类型参数被错误地用字符串包裹：

EXEC Usp_saveinvoicepayment_lko 
    NULL, 123, '87.09', '2019-07-11', 'Debit', 'test', 12, '2019-08-11', 1, 'test', 
    'LP/0819/0000183', 'yES', '90.09', '9875', '7', '2019-07-18', 988893739, '28.09', '76.09', 
    @RESPONSECODE out, @RESPONSEMESSAGE out, @IDRESPONSE out

其中'87.09'、'90.09'、'28.09'、'76.09'是字符串，但存储过程对应的参数@PayableAmount、@PreviousBalance、@Latitude、@Longitude都是NUMERIC(10,2)类型。虽然SQL Server有时会做隐式转换，但这种写法很容易引发转换错误，尤其是当字符串格式不符合要求时。

修复方案： #

去掉数值参数的引号，直接传入数值：

EXEC Usp_saveinvoicepayment_lko 
    NULL, 123, 87.09, '2019-07-11', 'Debit', 'test', 12, '2019-08-11', 1, 'test', 
    'LP/0819/0000183', 'yES', 90.09, '9875', '7', '2019-07-18', 988893739, 28.09, 76.09, 
    @RESPONSECODE out, @RESPONSEMESSAGE out, @IDRESPONSE out

额外建议 #

• 对于@CollectorMobile参数，当前定义为INT类型，但手机号码通常长度较长，建议改为VARCHAR(20)类型，避免出现超出INT范围的号码导致报错。
• 在存储过程中加入参数校验逻辑，比如检查数值类型参数是否合法，提前拦截无效输入引发的转换错误。

内容的提问来源于stack exchange，提问作者S.Sehgal