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

Question

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

If at all possible, it would be better to store the prefix and the number in separate columns - it makes code such as the above drastically easier to write. If the prefix is always J that need not be stored at all. And you can always create a computed column that combines them back together in a single column, if you really need that. — Damien_The_Unbeliever
– Damien_The_Unbeliever, Commented Jul 10, 2013 at 10:18
I'm also struggling to understand why you'd CAST the MAX result back to a varchar, just to, on the next line, CAST it back to int. — Damien_The_Unbeliever
– Damien_The_Unbeliever, Commented Jul 10, 2013 at 10:21
actually i need the result as J1,J2 so that it can be showed in the application as voucher number and also stored in the single column. — naren
– naren, Commented Jul 10, 2013 at 10:22
you shouldn't base your database structure around your display or formatting requirements. Logically, the number and the prefix are two separate data items on which you want to perform processing - they ought to be in separate columns. — Damien_The_Unbeliever
– Damien_The_Unbeliever, Commented Jul 10, 2013 at 10:25

Mohammad Areeb Siddiqui · Accepted Answer · 2013-07-10 12:38:21Z

5

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

edited Jul 10, 2013 at 12:38

Mohammad Areeb Siddiqui

10.2k16 gold badges74 silver badges118 bronze badges

answered Jul 10, 2013 at 11:31

Jaychand

661 bronze badge

Sign up to request clarification or add additional context in comments.

2 Comments

naren Over a year ago

after editing it worked but why it is adding the number after the prefix 'J'?

Jodrell Over a year ago

@droid_rush, this answer identifies the same problem as my answer, just an hour later. stackoverflow.com/a/17568151/659190.

live-love · Accepted Answer · 2014-02-11 14:03:34Z

5

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.

answered Feb 11, 2014 at 14:03

live-love

53.2k27 gold badges257 silver badges220 bronze badges

Comments

Jakub Konecki · Accepted Answer · 2013-07-10 10:18:42Z

2

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.

answered Jul 10, 2013 at 10:18

Jakub Konecki

46.1k8 gold badges92 silver badges128 bronze badges

Comments

Jodrell · Accepted Answer · 2013-07-10 10:35:18Z

2

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.

edited Jul 10, 2013 at 10:35

answered Jul 10, 2013 at 10:20

Jodrell

35.8k6 gold badges94 silver badges131 bronze badges

Comments

Devart · Accepted Answer · 2013-07-10 10:20:45Z

0

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

answered Jul 10, 2013 at 10:20

Devart

120k23 gold badges172 silver badges189 bronze badges

1 Comment

Devart Over a year ago

CAST truncated value from @maxCode, so CAST(@maxCode AS VARCHAR(10) is equal to LEFT(@maxCode, 10).

Collectives™ on Stack Overflow

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

5 Answers 5

2 Comments

Comments

Comments

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

5 Answers 5

2 Comments

Comments

Comments

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related