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

最新推荐文章于 2024-08-14 07:03:06 发布

转载最新推荐文章于 2024-08-14 07:03:06 发布 · 263 阅读

0 ·

CC 4.0 BY-SA版权

原文链接：http://www.cnblogs.com/oceanho/p/4972674.html

文章标签：

#数据库

 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