Establish a table
Create Table [DBO]. [Tab] ([ID] [CHAR] (10) Collate Chinese_PRC_CI_AS NULL, [IDNAME] [CHAR] (10) Collate Chinese_PRC_CI_AS NULL) ON [PRIMARY] GO
Create a stored procedure
ID automatically adds YYMMDD four sequences
Such as: 0601110001, 06010110002, 0601220001 ...
Create Procedure SP_CREATEID @ IDNAME CHAR (10), @ RET_ERR INT = 0 OUTPUTAS
Declare @id char (10) Select Top 1 @ID = id from tabwhere left (ID, 6) = Replace (CHAR (10), getdate (), 2), '.', '') Order by ID Descif (@ID is not null) begin set @id = Replace (CHAR (10), getdate (), 2), '.', '') Right (10001 @ ID, 4) SELECT @ RET_ERR = 0 END ELSE BEGIN SET @ID = Replace (CONVERT (CHAR (10), Getdate (), 2), '.', '') '0001' SELECT @ RET_ERR = 1 End
INSERT INTO TAB (ID, IDNAME) VALUES (@ id, @ idname) GO
test:
Declare @ret as intexec sp_createid 'Name, @ Ret OutputPrint @ret
