在sql中将varchar值转换为数据类型int时转换失败

I wrote the store procedure which should return the values like-

我写了一个应该返回值的商店程序 -

J1
J2
J3

I have table named Journal_Entry. When the row count of the table is 0, it gives the result J1 but as the row count increases it shows the error-

我有一个名为Journal_Entry的表。当表的行数为0时,它给出结果J1,但随着行数增加,它显示错误 -

"Conversion failed when converting the varchar value 'J' to data type int."

#here the Voucher_No is the column for the result to be saved.

#here Voucher_No是要保存的结果的列。

The code is like-

代码如下 -

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

5 个解决方案

#1

The problem located on the following line

问题位于以下行

SELECT  @Prefix + LEN(CAST(@maxCode AS VARCHAR(10))+1) + CAST(@maxCode AS VARCHAR(100))

Use this instead

改用它

SELECT  @Prefix + CAST(LEN(CAST(@maxCode AS VARCHAR(10))+1) AS VARCHAR(100)) + CAST(@maxCode AS VARCHAR(100))

Full Code:

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(@startFrom)+1,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

#2

I got the same error message. In my case, it was due to not using quotes.

我收到了同样的错误消息。就我而言,这是由于没有使用引号。

Although the column was supposed to have only numbers, it was a Varchar column, and one of the rows had a letter in it.

虽然该列应该只有数字,但它是一个Varchar列,其中一行中有一个字母。

So I was doing this:

所以我这样做:

select * from mytable where myid = 1234

While I should be doing this:

虽然我应该这样做:

select * from mytable where myid = '1234'

If the column had all numbers, the conversion would have worked, but not in this case.

如果列具有所有数字,则转换将起作用,但在这种情况下不起作用。

#3

Your problem seams to be located here:

您的问题接缝位于此处:

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)

As the error says, you're casting a string that contains a letter 'J' to an INT which for obvious reasons is not possible.

正如错误所示,您正在向INT投射包含字母'J'的字符串,这显然是不可能的。

Either fix SUBSTRING or don't store the letter 'J' in the database and only prepend it when reading.

要么修复SUBSTRING,要么不在数据库中存储字母“J”,只在读取时添加前缀。

#4

The line

SELECT  @Prefix + LEN(CAST(@maxCode AS VARCHAR(10))+1) + CAST(@maxCode AS VARCHAR(100))

is wrong.

@Prefix is 'J' and LEN(...anything...) is an int, hence the type mismatch.

@Prefix是'J'和LEN(......任何......)是一个int,因此类型不匹配。

It seems to me, you actually want to do,

在我看来,你真的想做,

SELECT
        @maxCode = MAX(
            CAST(SUBSTRING(
                Voucher_No,
                @startFrom + 1,
                LEN(Voucher_No) - (@startFrom + 1)) AS INT)
    FROM
        dbo.Journal_Entry;

SELECT  @Prefix + CAST(@maxCode AS VARCHAR(10));

but, I couldn't say. If you illustrated before and after data, it would help.

但是,我不能说。如果您在数据之前和之后进行说明,那将会有所帮助。

#5

Try this one -

试试这个 -

CREATE PROC [dbo].[getVoucherNo]
AS BEGIN

     DECLARE 
            @Prefix VARCHAR(10) = 'J'
          , @startFrom INT = 1
          , @maxCode VARCHAR(100)
          , @sCode INT

     IF EXISTS(
          SELECT 1 
          FROM dbo.Journal_Entry
     ) BEGIN

          SELECT @maxCode = CAST(MAX(CAST(SUBSTRING(Voucher_No,LEN(@startFrom)+1,ABS(LEN(Voucher_No)- LEN(@Prefix))) AS INT)) AS varchar(100)) 
          FROM dbo.Journal_Entry;

          SELECT @Prefix + 
               CAST(LEN(LEFT(@maxCode, 10) + 1) AS VARCHAR(10)) + -- !!! possible problem here
               CAST(@maxCode AS VARCHAR(100))

     END
     ELSE BEGIN

          SELECT (@Prefix + CAST(@startFrom AS VARCHAR)) 

     END

END

#1