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   gt  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

User · Answer

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

User · Answer

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   gt  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

User · Answer

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

User · Answer

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

User · Answer

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

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

Examples related to sql-server

Examples related to tsql

Examples related to sqldatatypes