当将varchar值“PK3”转换为数据类型int时，转换失败

I have a stored procedure to insert data in a pattern,

我有一个将数据插入模式的存储过程，

DECLARE @I INT = 1
DECLARE @ID INT = 0
DECLARE @PackID VARCHAR(50)

SELECT @PackID = MAX(PackID) 
FROM tblPacks 
WHERE PackID LIKE 'PK%'

IF(@String = 'Packs')
BEGIN
    WHILE @I <= @Count
    BEGIN
        IF @PackID IS NULL
        BEGIN
            SET @I = @I + 1
            SET @ID = @ID + 1

            INSERT INTO tblPacks (T_ID, BatchNumber, PackID, Status, BlistersCount)
            VALUES (@T_ID, @BatchNumber,
                    'PK'+ CAST(@ID as VARCHAR(50)), 0,
                    (SELECT NumberOfBlistersInEachPack 
                     FROM tblPackDetails 
                     WHERE T_ID = @T_ID AND BatchNumber = @BatchNumber))
        END        
        ELSE
        BEGIN 
            SET @I = @I + 1
          error :-  SELECT @ID = (SELECT MAX(PackID) FROM tblPacks)
            SET @ID = @ID + 1

            INSERT INTO tblPacks (T_ID, BatchNumber, PackID, Status,  BlistersCount)  
             VALUES (@T_ID, @BatchNumber,
                     'PK'+ CAST(@ID as VARCHAR(50)), 0,
                     (SELECT NumberOfBlistersInEachPack 
                      FROM tblPackDetails 
                      WHERE T_ID = @T_ID AND BatchNumber = @BatchNumber))
            END
        END
    END
END

It is showing an error:

它显示了一个错误:

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

当将varchar值“PK3”转换为数据类型int时，转换失败。

If packid is null then it Should be 'PK1' and if it has some data then it will take max value and then it should be incremented by'1'. Where should I modify my code to get rid of this error?

如果packid为空，那么它应该是PK1如果它有一些数据那么它将取最大值然后它应该增加1。我应该在哪里修改代码以摆脱这个错误?

4 个解决方案

#1

Try to put the below statement in your insert query

尝试将下面的语句放到insert查询中

 ('PK'+CAST(@ID as VARCHAR(50)))

Here first we need to convert integer type to varchar then concatenate it.

首先，我们需要将整型类型转换为varchar，然后将它连接起来。

#2

Try this once it may help you. First convert the integer variable to string and then concatenate with string variable. Add this chunk in your query and enjoy..

尝试一下，这可能对你有帮助。首先将整型变量转换为字符串，然后与字符串变量连接。在您的查询中添加此块并享受。

'PK'+ CAST(@ID as VARCHAR(50))

DECLARE @I INT=1
        DECLARE @ID INT=0
        DECLARE @PackID VARCHAR(50)


    SELECT @PackID=MAX(PackID) FROM tblPacks WHERE PackID LIKE 'PK%'

    IF(@String='Packs')
    BEGIN
    WHILE @I<=@Count
    BEGIN
    IF @PackID IS NULL
    BEGIN
    SET @I=@I+1
    SET @ID=@ID+1
    INSERT INTO tblPacks (T_ID,BatchNumber,PackID,Status,BlistersCount)
    VALUES (
        @T_ID,@BatchNumber,
     'PK'+ CAST(@ID as VARCHAR(50)) ,0,(SELECT NumberOfBlistersInEachPack FROM tblPackDetails WHERE T_ID=@T_ID AND BatchNumber=@BatchNumber))

    END

    ELSE
    BEGIN 
    SET @I=@I+1
    SELECT @ID=(SELECT MAX(PackID) FROM tblPacks)
    SET @ID=@ID+1
    INSERT INTO tblPacks (T_ID,BatchNumber,PackID,Status,BlistersCount)
    VALUES (
        @T_ID,@BatchNumber,
      'PK'+ CAST(@ID as VARCHAR(50)) ,0,(SELECT NumberOfBlistersInEachPack FROM tblPackDetails WHERE T_ID=@T_ID AND BatchNumber=@BatchNumber))
    END
    END
    END
END

#3

You also can you some string function to convert the int to varchar,for example:

您还可以使用一些字符串函数将int转换为varchar，例如:

DECLARE @ID INT =100
SELECT 'PK'+LTRIM(@ID)

#4

Please specify the line# in which you are getting error. This is just a conversion error. Please specify the table definition as well. Refer the below query. I have changed the concatenation part. If the error still persists, please share the table definition and errored line#. Also what is the data type of PackID column in tblPacks? The PackID is non numeric and you are trying to assign that value to @ID which is declared as INT. This is the problem. I strongly suggest using some other value instead of taking the Maximum of a string. I have modified the query again to take maximum from the SUBSTRING of PackID

请指定出现错误的行号。这只是一个转换错误。还请指定表定义。请参考以下查询。我更改了连接部分。如果错误仍然存在，请共享表定义和出错的行#。另外，任务包中的PackID列的数据类型是什么?PackID是非数字的，您正在尝试将这个值赋给@ID，它被声明为INT，这就是问题所在。我强烈建议使用其他值而不是字符串的最大值。我再次修改了查询，从PackID的子字符串中获取最大值

DECLARE @I INT = 1
DECLARE @ID INT = 0
DECLARE @MaxPackID VARCHAR(50)
DECLARE @PackID VARCHAR(50)


SELECT @PackID = MAX(PackID) 
FROM tblPacks 
WHERE PackID LIKE 'PK%'

IF(@String = 'Packs')
BEGIN
    WHILE @I <= @Count
    BEGIN
        IF @PackID IS NULL
        BEGIN
            SET @I = @I + 1
            SET @ID = @ID + 1

            INSERT INTO tblPacks (T_ID, BatchNumber, PackID, Status, BlistersCount)
            VALUES (@T_ID, @BatchNumber,
                    'PK' + CAST(@ID AS VARCHAR(50)), 0,
                    (SELECT NumberOfBlistersInEachPack 
                     FROM tblPackDetails 
                     WHERE T_ID = @T_ID AND BatchNumber = @BatchNumber))
        END        
        ELSE
        BEGIN 
            SET @I = @I + 1
            SELECT @MaxPackID = MAX(SUBSTRING(PackID,PATINDEX('%[0-9]%',PackID),LEN(PackID))) FROM tblPacks
            SELECT @ID = @MaxPackID
            SET @ID = @ID + 1

            INSERT INTO tblPacks (T_ID, BatchNumber, PackID, Status,  BlistersCount)  
             VALUES (@T_ID, @BatchNumber,
                     'PK' + CAST(@ID AS VARCHAR(50)), 0,
                     (SELECT NumberOfBlistersInEachPack 
                      FROM tblPackDetails 
                      WHERE T_ID = @T_ID AND BatchNumber = @BatchNumber))
            END
        END
    END
END