SQL Server序列号的获取

建表:

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=NMS_Description, @value=N类型(1-任务单号) , @level0type=NSCHEMA,@level0name=Ndbo, @level1type=NTABLE,@level1name=NWCS_Seq, @level2type=NCOLUMN,@level2name=NSeqType
18 GO
19 
20 EXEC sys.sp_addextendedproperty @name=NMS_Description, @value=N序列值 , @level0type=NSCHEMA,@level0name=Ndbo, @level1type=NTABLE,@level1name=NWCS_Seq, @level2type=NCOLUMN,@level2name=NSeqVal
21 GO
22 
23 EXEC sys.sp_addextendedproperty @name=NMS_Description, @value=N序列时间 , @level0type=NSCHEMA,@level0name=Ndbo, @level1type=NTABLE,@level1name=NWCS_Seq, @level2type=NCOLUMN,@level2name=NSeqTime
24 GO
25 
26 EXEC sys.sp_addextendedproperty @name=NMS_Description, @value=N序列表 , @level0type=NSCHEMA,@level0name=Ndbo, @level1type=NTABLE,@level1name=NWCS_Seq
27 GO
View Code

获取序列:

SQL Server序列号的获取
 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

使用方法:

SQL Server序列号的获取
 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

SQL Server序列号的获取

上一篇:【20200407】MySQL账号不规则删除导致权限错误


下一篇:CTF赛事之SQL注入(一篇让你认识它)