I think this code solves the problem of concurrent inserts. One SELECT is used to generate the new invoice id, but if another user manages to sneak it in first the code loops and tries the next number.
@tmp_date = RIGHT(CAST(YEAR(GETDATE()) + CASE WHEN @year_flag = 'P' THEN -1 ELSE 0 END AS CHAR(4)),2) + '-'
while 1=1 begin
SET @new_invoice_id = @tmp_date
(SELECT MAX(invoice_id) FROM dbo.Invoice WHERE invoice_id LIKE (@tmp_date + '%'))
,5) AS int) + 100001
/* For testing purposes, uncomment the following code to randomly insert conflicting records
if rand() > 0.6
insert into Invoice (invoice_id, customer_name, invoice_amount) values(@new_invoice_id, @customer_name, @invoice_amount)
insert into Invoice (invoice_id, customer_name, invoice_amount)
select @new_invoice_id, @customer_name, @invoice_amount
where not exists(select null from Invoice with(holdlock) where invoice_id = @new_invoice_id)
if @@ROWCOUNT > 0 break