Increment not working in stored procedure

Question

i am using this sp i need to increment 010000 after that 010001 like that but it will not increment

 

ALTER procedure [dbo].[Sales_Recept]

as

begin

(SELECT BILLNO =

CASE

WHEN COUNT(bill_of_sale) = 0 THEN '000001'

WHEN Len(Max(CONVERT(int, Substring(bill_of_sale,1,6)))) = 1 AND Max(CONVERT(int, Substring(bill_of_sale,1,6))) < 9 THEN '00000' + Convert(Varchar(6),(Max(CONVERT(int, Substring(bill_of_sale,1,6)))) + 1)

WHEN Len(Max(CONVERT(int, Substring(bill_of_sale,1,6)))) = 1 AND Max(CONVERT(int, Substring(bill_of_sale,1,6))) = 9 THEN '0000' + Convert(Varchar(6),(Max(CONVERT(int, Substring(bill_of_sale,1,6)))) + 1)

WHEN Len(Max(CONVERT(int, Substring(bill_of_sale,1,6)))) = 2 AND Max(CONVERT(int, Substring(bill_of_sale,1,6))) < 99 THEN '0000' + Convert(Varchar(6),(Max(CONVERT(int, Substring(bill_of_sale,1,6)))) + 1)

WHEN Len(Max(CONVERT(int, Substring(bill_of_sale,1,6)))) = 2 AND Max(CONVERT(int, Substring(bill_of_sale,1,6))) = 99 THEN '000' + Convert(Varchar(6),(Max(CONVERT(int, Substring(bill_of_sale,1,6)))) + 1)

WHEN Len(Max(CONVERT(int, Substring(bill_of_sale,1,6)))) = 3 AND Max(CONVERT(int, Substring(bill_of_sale,1,6))) < 999 THEN '000' + Convert(Varchar(6),(Max(CONVERT(int, Substring(bill_of_sale,1,6)))) + 1)

WHEN Len(Max(CONVERT(int, Substring(bill_of_sale,1,6)))) = 3 AND Max(CONVERT(int, Substring(bill_of_sale,1,6))) = 999 THEN '00' + Convert(Varchar(6),(Max(CONVERT(int, Substring(bill_of_sale,1,6)))) + 1)

WHEN Len(Max(CONVERT(int, Substring(bill_of_sale,1,6)))) = 4 AND Max(CONVERT(int, Substring(bill_of_sale,1,6))) < 9999 THEN '00' + Convert(Varchar(6),(Max(CONVERT(int, Substring(bill_of_sale,1,6)))) + 1)

WHEN Len(Max(CONVERT(int, Substring(bill_of_sale,1,6)))) = 4 AND Max(CONVERT(int, Substring(bill_of_sale,1,6))) > 9999 THEN '0' + Convert(Varchar(6),(Max(CONVERT(int, Substring(bill_of_sale,1,6)))) + 1)

END

FROM Sales_details)

end