sql-server - 世代序列证书No

Question

我有用于生成 CertificateNo 的块代码。我需要任何人检查我的块代码的性能

--Struct table config 
--drop table CertificateSequenceGen;
CREATE TABLE [dbo].[CertificateSequenceGen](
    [Bus] [varchar](15) NULL,
    [MonthNo] [varchar](4) NOT NULL,
    [DateStart] [date] NULL,
    [DateEnd] [date] NULL,
    [SequenceStart] [int] NOT NULL,
    [SequenceEnd] [int] NOT NULL,
    [CurrentNo] [int] NOT NULL
) ON [PRIMARY]

---initial default data into table
INSERT INTO [dbo].[CertificateSequenceGen]([Bus],[MonthNo],[DateStart],[DateEnd],[SequenceStart],[SequenceEnd],[CurrentNo])
VALUES('ABC','1902','2019-02-01','2019-03-01',1,9999999,1);

--Store for generate certificateN
--drop PROC prdGetCertificateNo
create PROC prdGetCertificateNo
        @Bus            varchar(15) = '',
        @Prefix         varchar(15) = '',
        @CertificateNo  varchar(31) OUTPUT
    as
    begin
            update 
                    CertificateSequenceGen
            set
                    @CertificateNo  = @Prefix + Bus + MonthNo + left (REPLICATE('0',7 - len(CurrentNo)) + cast (CurrentNo as varchar(7)),7),
                    CurrentNo       = CurrentNo + 1
            where
                    Bus = @Bus and GETDATE() between DateStart and DateEnd;
    end
/*
--execute procedure genarate the CertificateNo
    declare @Prefix varchar(15) = 'YZ', @ProductCode varchar(15) = 'ABC', @CertificateNo varchar(31);
    exec [dbo].[prdGetCertificateNo] @ProductCode, @Prefix, @CertificateNo output

    select @CertificateNo;
*/

下面的执行过程是结果--result--- YZABC19020000001

标签： sql-server