1 IF EXISTS(SELECT TOP 1 * FROM sys.tables WHERE name=N'EmployeeNo_Identity')
2 DROP TABLE EmployeeNo_Identity
3 GO
4
5 CREATE TABLE EmployeeNo_Identity(
6 ID BIGINT NOT NULL IDENTITY(1000000,1) PRIMARY KEY,
7 CreateDate DATETIME DEFAULT(GETDATE())
8 )
9
10
11 IF EXISTS(SELECT TOP 1 * FROM SYS.procedures WHERE name=N'proc_Build_Employee_ID')
12 DROP PROC proc_Build_Employee_ID
13 GO
14
15 CREATE PROC proc_Build_Employee_ID
16 @prefix NCHAR(1),
17 @result nvarchar(8) OUT
18 AS
19 BEGIN
20 DECLARE @id BIGINT
21 INSERT INTO EmployeeNo_Identity(CreateDate) VALUES(GETDATE())
22 SELECT @id=MAX(ID) FROM EmployeeNo_Identity NOLOCK
23 DELETE EmployeeNo_Identity
24 SET @result=CAST( @prefix+CONVERT(NVARCHAR(7),@id) AS NVARCHAR(8))
25 END
26 GO
27
28
29 /** 调用 **/
30 DECLARE @result NVARCHAR(8)
31 EXEC [dbo].[proc_Build_Employee_ID]
32 @prefix = N'E',
33 @result=@result OUT
34 SELECT @result
