我写了商店程序,它应该返回如下的值：
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
