1 create table RefNoSeed( 2     prefix char(4), 3     seed int 4 ) 5  6 create table RefNoTable( 7     insertTime datetime unique, 8     RefNo varchar(20) unique 9 )10 11 if OBJECT_ID(‘P_GenerateRefNo‘,‘P‘) is not null12     drop proc P_GenerateRefNo13 go14 create proc P_GenerateRefNo15     @prefix varchar(20),16     @timeNow datetime17 as18     begin try19         begin tran20             set nocount on;21             declare @newSeed int22             set @newSeed=(select seed from RefNoSeed where prefix=@prefix)+123             if @newSeed is null24                 begin25                     insert into RefNoSeed values(@prefix,0)26                     set @newSeed=127                 end28             insert into RefNoTable (insertTime,RefNo)values(@timeNow,@prefix+cast(YEAR(@timeNow) as CHAR(4))+cast(@newSeed as CHAR(10)))29             update RefNoSeed set seed=seed+1 where prefix=@prefix30         commit31     end try32     begin catch33         rollback34     end catch35 go36 37 declare @timenow datetime38 set @timenow=GETDATE()39 exec P_GenerateRefNo ‘AB‘,@timenow40 waitfor delay ‘00:00:00.15‘41 set @timenow=GETDATE()42 exec P_GenerateRefNo ‘AB‘,@timenow43 waitfor delay ‘00:00:00.15‘44 set @timenow=GETDATE()45 exec P_GenerateRefNo ‘AB‘,@timenow46 waitfor delay ‘00:00:00.15‘47 set @timenow=GETDATE()48 exec P_GenerateRefNo ‘AB‘,@timenow49 waitfor delay ‘00:00:00.15‘50 set @timenow=GETDATE()51 exec P_GenerateRefNo ‘BF‘,@timenow52 waitfor delay ‘00:00:00.15‘53 set @timenow=GETDATE()54 exec P_GenerateRefNo ‘BF‘,@timenow55 waitfor delay ‘00:00:00.15‘56 set @timenow=GETDATE()57 exec P_GenerateRefNo ‘PF‘,@timenow58 waitfor delay ‘00:00:00.15‘59 set @timenow=GETDATE()60 exec P_GenerateRefNo ‘PF‘,@timenow61 waitfor delay ‘00:00:00.15‘62 set @timenow=GETDATE()63 exec P_GenerateRefNo ‘PF‘,@timenow64 waitfor delay ‘00:00:00.15‘65 set @timenow=GETDATE()66 exec P_GenerateRefNo ‘RG‘,@timenow67 68 select*from RefNoSeed order by prefix69 select*from RefNoTable order by REFNO70 71 delete RefNoSeed72 delete RefNoTable73 74 drop table RefNoSeed75 drop table RefNoTable