Using SQL tables to generate unique IDs by date series

1. Create a table to save the largest ID now

SELECT [Id],[prefix],[code] from [Documentno]

2. Increase the SP, using the lock table, generate the corresponding ID

Create PROCEDURE [dbo].[Generaldocumentno]( @preFix nvarchar( the), @outCode varchar( the) output) asBEGIN-- =============================================--Author:xiefs--Create date:2013-12-18--Description: Generate system unique serial number-- =============================================Declare @docNo nvarchar( the),        @month varchar(Ten),        @day varchar(Ten),        @no varchar(Ten),        @num int ,        @lenNum int,        @sql nvarchar(4000),        @maxCode varchar( the),        @tableName varchar( the)  SETNOCOUNT on; Set @tableName='Documentno'; Set @month=CAST(Month(getdate()) as varchar); Set @day = CAST( Day(getdate()) as varchar); if(LEN(@month)=1)Set @month='0'+@month; if(LEN(@day)=1)Set @day='0'+@day; Set @docNo = @preFix+cast( Year(getdate()) as varchar)+ @month+@day; Set @sql =N'Select top 1 @p=code from'+@tableName +'With (Holdlock,tablockx) where CHARINDEX (" "+@docNo+" ", code) =1 ORDER BY id DESC'; EXECsp_executesql@sqlN'@p varchar (output)',@p=@maxCodeoutput;if(@maxCode  is NULL)  begin  Set @no='1'; End; Else begin  Set @no=CAST((CAST(SUBSTRING(@maxCode,Len(@docNo)+1,LEN(@maxCode)) as int)+1) as varchar); End; Set @lennum = LEN(@no); Set @num =4;  while @lenNum<4 begin Set @lenNum=@lenNum+1; Set @no='0'+@no; End Insert  intoDocumentno (Prefix,code)Values(@preFix,@docNo+@no); --select @[email protected]; Set @outCode=@docNo+@no;END

Using SQL tables to generate unique IDs by date series