Conversion failed when converting the varchar value to data type int in sql

naren picture naren · Jul 10, 2013 · Viewed 125k times · Source

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-

"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.

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

Answer

Jaychand picture Jaychand · Jul 10, 2013

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