SQL Server序列号的获取

建表：

 1 USE [JX_IMS_CPK]
 2 GO
 3 
 4 SET ANSI_NULLS ON
 5 GO
 6 
 7 SET QUOTED_IDENTIFIER ON
 8 GO
 9 
10 CREATE TABLE [dbo].[WCS_Seq](
11     [SeqType] [int] NOT NULL,
12     [SeqVal] [bigint] NOT NULL,
13     [SeqTime] [datetime] NOT NULL
14 ) ON [PRIMARY]
15 GO
16 
17 EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'类型(1-任务单号)' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'WCS_Seq', @level2type=N'COLUMN',@level2name=N'SeqType'
18 GO
19 
20 EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'序列值' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'WCS_Seq', @level2type=N'COLUMN',@level2name=N'SeqVal'
21 GO
22 
23 EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'序列时间' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'WCS_Seq', @level2type=N'COLUMN',@level2name=N'SeqTime'
24 GO
25 
26 EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'序列表' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'WCS_Seq'
27 GO
View Code

获取序列：

 1 USE [JX_IMS_CPK]
 2 GO
 3 
 4 SET ANSI_NULLS ON
 5 GO
 6 SET QUOTED_IDENTIFIER ON
 7 GO
 8 ALTER    PROCEDURE    [dbo].[WCS_GetTaskCode]
 9     @O_TaskCode    VARCHAR(50)=NULL    OUTPUT
10 --获取【任务号】
11 AS
12     BEGIN
13         DECLARE    @V_SeqType        INT=1,            --类型
14                 @SeqCount        INT,
15                 @NowTime        DATETIME=CAST(GETDATE()    AS    DATE),
16                 @SeqVal            BIGINT,
17                 @SeqTime        DATETIME,
18                 @NextVal        BIGINT=1;
19         
20         SELECT  @SeqCount = COUNT(1)
21         FROM    WCS_Seq    a
22         WHERE    a.SeqType=@V_SeqType;
23         
24         IF    @SeqCount = 0
25             BEGIN
26                 INSERT    INTO    WCS_Seq
27                 SELECT  @V_SeqType,@NextVal,@NowTime;
28                 SET    @O_TaskCode=RIGHT(CONVERT(VARCHAR(50),@NowTime,112),6)+RIGHT('0000'+CAST(@NextVal    AS    VARCHAR(4)),4);
29                 SELECT    @O_TaskCode;
30                 RETURN;
31             END;
32         
33         SELECT TOP 1
34                 @SeqVal = a.SeqVal ,
35                 @SeqTime = a.SeqTime
36         FROM    WCS_Seq a
37         WHERE    a.SeqType=@V_SeqType;
38         
39         IF @SeqTime = @NowTime
40             SET @NextVal = @SeqVal + 1;
41         ELSE
42             SET @NextVal = 1;
43         
44         UPDATE  a
45         SET     a.SeqVal = @NextVal ,
46                 a.SeqTime = @NowTime
47         FROM    WCS_Seq a
48         WHERE   a.SeqVal = @SeqVal
49             AND    a.SeqType=@V_SeqType;
50         
51         SET    @O_TaskCode=RIGHT(CONVERT(VARCHAR(50),@NowTime,112),6)+RIGHT('0000'+CAST(@NextVal    AS    VARCHAR(4)),4);
52     END;
View Code

使用方法：

 1 USE [JX_IMS_CPK]
 2 GO
 3 
 4 DECLARE    @return_value int,
 5         @O_TaskCode varchar(50)
 6 
 7 EXEC    @return_value = [dbo].[WCS_GetTaskCode]
 8         @O_TaskCode = @O_TaskCode OUTPUT
 9 
10 SELECT    @O_TaskCode as N'@O_TaskCode'
11 
12 SELECT    'Return Value' = @return_value
13 
14 GO
View Code

结果：

2004080008

0

posted @ 2020-04-08 14:06  Bruce_Cheung  阅读(1143)  评论(0)  编辑  收藏  举报