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