Conversion failed when converting the varchar value 'BB-2' to data type int.

Question

I am inserting BB-1 ,then again ,BB-2 is generating , it is giving error.

Conversion failed when converting the varchar value 'BB-2' to data type int.

CREATE TABLE #tbl_Sell_M(Bill_No varchar(10),E_Date date)




WITH cte AS (SELECT 1 AS Bill_No
                          UNION ALL
                          SELECT TOP (1) Bill_No + 1 AS Bill_No
                          FROM     dbo.tbl_Sell_M AS t
                          ORDER BY Bill_No DESC)
    SELECT TOP (1)  CONCAT('BB-', Bill_No)  AS Bill_No
    FROM     cte
    ORDER BY Bill_No DESC

Answer 1

If tbl_Sell_M is empty then it works. But if it contains something like ‘BB-1’ or ‘BB-2’, then it is not possible to calculate Bill_No + 1.

Consider using integers (int column) without ‘BB-‘ (which can be prepended later), or try this workaround, which is probably less recommended:

WITH cte AS (SELECT 1 AS Bill_No
                           UNION ALL
                           SELECT TOP (1) stuff(Bill_No, 1, 3, '') + 1 AS Bill_No
                           FROM     tbl_Sell_M AS t
                           ORDER BY Bill_No DESC)
     SELECT TOP (1)  CONCAT('BB-', Bill_No)  AS Bill_No
     FROM     cte
     ORDER BY Bill_No DESC

Answer 2

Please post table design of the table as DDL, some sample data as DML and the expected result.
.
By the error message you try to mix varchar & int values; that don't work in SQL, you have to convert the values to same data type.

Answer 3

CREATE TABLE #tbl_Sell_M( Bill_No varchar(10) ,E_Date date)

As expected, Bill_No is of type varchar, you can not "add" numeric 1 on the values. How does the existing data (=samples, as requested) look like?