Conversion error when converting varchar value to int data type in sql

Question

I wrote a repository procedure that should return values, for example -

J1 J2 J3 

I have a table called Journal_Entry . When the number of rows in the table is 0, it gives the result J1 , but as the number of rows increases, it shows an error -

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

#here Voucher_No is a column to save the result.

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 1

The problem is located on the next 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 

Answer 2

I have the same error message. In my case, this happened due to not using quotes.

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

So, I did this:

 select * from mytable where myid = 1234 

So far I have to do this:

 select * from mytable where myid = '1234' 

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

Answer 3

Your problematic seams should 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 produce a string containing the letter "J" in INT , which for obvious reasons is not possible.

Either fix SUBSTRING , or do not store the letter "J" in the database and only add it when reading.

Answer 4

Line

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

wrong.

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

---

I think you really 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 could not say. If you illustrated before and after the data, this will help.

Answer 5

Try this option -

 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