[sql-server] Conversion failed when converting the varchar value to data type int in sql

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

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.

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.

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

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.

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

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.

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.