我写了商店程序,它应该返回如下的值:
J1 J2 J3
我有一个名为Journal_Entry的表.当表的行数为0时,它给出结果J1,但随着行数的增加,它显示错误 –
"Conversion Failed when converting the varchar value 'J' to data type int."
#here Voucher_No是要保存的结果的列.
代码如下 –
CREATE PROC [dbo].[getVoucherNo] AS BEGIN DECLARE @Prefix VARCHAR(10)='J' DECLARE @startFrom INT=1 DECLARE @maxCode VARCHAR(100) DECLARE @sCode INT IF((SELECT COUNT(*) FROM dbo.Journal_Entry) > 0) BEGIN SELECT @maxCode = CAST(MAX(CAST(SUBSTRING(Voucher_No,LEN(@startFrom)+1,LEN(Voucher_No)- LEN(@Prefix)) AS INT)) AS varchar(100)) FROM dbo.Journal_Entry; SET @sCode=CAST(@maxCode AS INT) SELECT @Prefix + LEN(CAST(@maxCode AS VARCHAR(10))+1) + CAST(@maxCode AS VARCHAR(100)) END ELSE BEGIN SELECT(@Prefix + CAST(@startFrom AS VARCHAR)) END END
解决方法
问题位于以下行
SELECT @Prefix + LEN(CAST(@maxCode AS VARCHAR(10))+1) + CAST(@maxCode AS VARCHAR(100))
请改用它
SELECT @Prefix + CAST(LEN(CAST(@maxCode AS VARCHAR(10))+1) AS VARCHAR(100)) + CAST(@maxCode AS VARCHAR(100))
完整代码:
CREATE PROC [dbo].[getVoucherNo] AS BEGIN DECLARE @Prefix VARCHAR(10)='J' DECLARE @startFrom INT=1 DECLARE @maxCode VARCHAR(100) DECLARE @sCode INT IF((SELECT COUNT(*) FROM dbo.Journal_Entry) > 0) BEGIN SELECT @maxCode = CAST(MAX(CAST(SUBSTRING(VoucharNo,LEN(VoucharNo)- LEN(@Prefix)) AS INT))+1 AS varchar(100)) FROM dbo.Journal_Entry; SET @sCode=CAST(@maxCode AS INT) SELECT @Prefix + CAST(LEN(CAST(@maxCode AS VARCHAR(10))+1) AS VARCHAR(100)) + CAST(@maxCode AS VARCHAR(100)) END ELSE BEGIN SELECT(@Prefix + CAST(@startFrom AS VARCHAR)) END END