SQL Server 存储过程自定义生成ID号 

 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

 

posted on 2015-11-17 19:48  oceanho  阅读(963)  评论(0)    收藏  举报

导航