Transaction Id Creation Logic

See more:

ASP.NET

SQL

Dear All,

I have a system and i want to create unique id's on daily basis.
Daily Transaction id should start with 1

ex:
Today
2606140001
2606140002
2606140003
2606140004
2606140005
2606140006
.......
2606140010

Tomorrow
2706140001
2706140002
2706140003
2706140004
2706140005
2706140006
.......
2706140010

Here is what i have done so far...

USE [BPOPortal]
GO
/****** Object:  StoredProcedure [dbo].[GetScanId]    Script Date: 6/26/2014 3:39:22 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[GetScanId]
@id int,
	@dtTime datetime
AS
BEGIN
if @id=1
begin
set @dtTime=GETDATE()
--set @dtTime='06/17/2014'

declare @TransactionId varchar(20)
declare @EScanId varchar(20)
begin tran


	if (select Top(1) CONVERT(date,[DateTime],110) from EScantbl)=CONVERT(date,@dtTime,110)
	begin
			set @TransactionId=(select Top(1) ScanId from UploadMaster order by id desc)
			set @TransactionId=Convert(int,SUBSTRING(@TransactionId,LEN(@TransactionId)-3,4))
			set @EScanId=(select EScanId from EScantbl)
			update EScantbl set EScanId=EScanId+1
				select @TransactionId+1 as EScanId
	end
	else
	begin
		update EScantbl set EScanId='001' ,[DateTime]=@dtTime
		select EScanId from EScantbl
	end
end
else 
begin
	select 1
end
commit
 end

Above code works perfectly but the issue is when i have concurrent users trying to get TransactionId sometimes it fails by creating same Transactionid twice.

This happens very frequently, its nearly 5 times out of 300 cases.
But its bad Code for me as Transaction Id is repeating.
Somebody please help.

Thanks In Advance,
SUNIL MALI.