SQL Server序列号的获取

建表：

USE [JX_IMS_CPK]
GO

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[WCS_Seq](
    [SeqType] [int] NOT NULL,
    [SeqVal] [bigint] NOT NULL,
    [SeqTime] [datetime] NOT NULL
) ON [PRIMARY]
GO

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'
GO

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'
GO

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'
GO

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'序列表' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'WCS_Seq'
GO

获取序列：

USE [JX_IMS_CPK]
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER    PROCEDURE    [dbo].[WCS_GetTaskCode]
    @O_TaskCode    VARCHAR(50)=NULL    OUTPUT
--获取【任务号】
AS
    BEGIN
        DECLARE    @V_SeqType        INT=1,            --类型
                @SeqCount        INT,
                @NowTime        DATETIME=CAST(GETDATE()    AS    DATE),
                @SeqVal            BIGINT,
                @SeqTime        DATETIME,
                @NextVal        BIGINT=1;
        
        SELECT  @SeqCount = COUNT(1)
        FROM    WCS_Seq    a
        WHERE    a.SeqType=@V_SeqType;
        
        IF    @SeqCount = 0
            BEGIN
                INSERT    INTO    WCS_Seq
                SELECT  @V_SeqType,@NextVal,@NowTime;
                SET    @O_TaskCode=RIGHT(CONVERT(VARCHAR(50),@NowTime,112),6)+RIGHT('0000'+CAST(@NextVal    AS    VARCHAR(4)),4);
                SELECT    @O_TaskCode;
                RETURN;
            END;
        
        SELECT TOP 1
                @SeqVal = a.SeqVal ,
                @SeqTime = a.SeqTime
        FROM    WCS_Seq a
        WHERE    a.SeqType=@V_SeqType;
        
        IF @SeqTime = @NowTime
            SET @NextVal = @SeqVal + 1;
        ELSE
            SET @NextVal = 1;
        
        UPDATE  a
        SET     a.SeqVal = @NextVal ,
                a.SeqTime = @NowTime
        FROM    WCS_Seq a
        WHERE   a.SeqVal = @SeqVal
            AND    a.SeqType=@V_SeqType;
        
        SET    @O_TaskCode=RIGHT(CONVERT(VARCHAR(50),@NowTime,112),6)+RIGHT('0000'+CAST(@NextVal    AS    VARCHAR(4)),4);
    END;

使用方法：

USE [JX_IMS_CPK]
GO

DECLARE    @return_value int,
        @O_TaskCode varchar(50)

EXEC    @return_value = [dbo].[WCS_GetTaskCode]
        @O_TaskCode = @O_TaskCode OUTPUT

SELECT    @O_TaskCode as N'@O_TaskCode'

SELECT    'Return Value' = @return_value

GO

结果：

2004080008

0