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

Question

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.

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?

Answer 1

Try to put the below statement in your insert query

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

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

Answer 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

Answer 3

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

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

Answer 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

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