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