^{<noscript id="xcwtw"></noscript>}

自動編號的存儲過程

2019-11-18 18:42:38

字體：大中小

來源：轉載

供稿：網友

CREATE PROCEDURE Get_BH @cL_MC char(20),@nL_Init Int AS
begin
Declare @nL_CD Numeric(2,0),@cL_LSH char(20),@cL_LX Char(1),@nL_CDT Int,@nL_CDM int
Declare LSHB_cursor Cursor For Select Cd,LSH,Lx From LSHB Where MC=@cL_MC
Open LSHB_cursor
Fetch Next From LSHB_Cursor into @nL_CD,@cL_LSH,@cL_LX
If @@FETCH_STATUS = 0
    begin
      Select @nL_CDM=Len(RTrim(Convert(Char(10),@nL_Init)))
      If @cL_LX='1'
        begin      -- 前面四位為年 '2000'
          Select @nL_CDT=@nL_CD-4
          If left(@cL_LSH,4)=convert(char(4),getdate(),102) /* 年份相等，序號加 1   */
            begin
              Select @nL_CDM=Len(RTrim(Convert(Char(20),Convert(int,Right(@cL_LSH,16))+1)))
       Update LSHB set LSH=Left(@cL_LSH,4)+Replicate('0',@nL_CDT-@nL_CDM)+Rtrim(Convert(Char(20),Convert(int,Right(@cL_LSH,16))+1))
                where MC=@cL_MC
            End
          else
            If @nL_Init>1
               Update LSHB set LSH=replace(Convert(Char(4),getdate(),102),'.','')+Replicate('0',@nL_CDT-@nL_CDM)+Rtrim(Convert(Char(10),@nL_Init))
                 where MC=@cL_MC
            Else
              Update LSHB set LSH=replace(Convert(Char(4),getdate(),102),'.','')+Replicate('0',@nL_CDT-1)+'1'
                where MC=@cL_MC
        End
      If @cL_LX='2'
        begin      -- 前面六位為年月 '200008'
          Select @nL_CDT=@nL_CD-6
          If left(@cL_LSH,4)=convert(char(4),getdate(),102) AND Convert(int,substring(@cL_LSH,5,2))=DATEPART(month,getdate())
            /* 年月相等，序號加 1   */
            begin
              Select @nL_CDM=Len(RTrim(Convert(Char(20),Convert(int,Right(@cL_LSH,14))+1)))
       Update LSHB set LSH=Left(@cL_LSH,6)+Replicate('0',@nL_CDT-@nL_CDM)+Rtrim(Convert(Char(20),Convert(int,Right(@cL_LSH,14))+1))
                where MC=@cL_MC
            End
          else
            If @nL_Init>1
               Update LSHB set LSH=replace(Convert(Char(7),getdate(),102),'.','')+Replicate('0',@nL_CDT-@nL_CDM)+RTrim(Convert(Char(10),@nL_Init))
                  where MC=@cL_MC
            Else
               Update LSHB set LSH=replace(Convert(Char(7),getdate(),102),'.','')+Replicate('0',@nL_CDT-1)+'1'
                  where MC=@cL_MC
        End
      If @cL_LX='3'
        begin   -- 前面八位為年月日 '20000910'
          Select @nL_CDT=@nL_CD-8
          If left(@cL_LSH,4)=convert(char(4),getdate(),102) AND Convert(int,substring(@cL_LSH,5,2))=DATEPART(month,getdate())
              AND Convert(Int,SubString(@cL_LSH,7,2))=DatePart(Day,Getdate())
            Begin
              Select @nL_CDM=Len(Rtrim(Convert(Char(20),Convert(Int,Right(@cL_LSH,12))+1)))
              Update LSHB Set LSH=Left(@cL_LSH,8)+Replicate('0',@nL_CDT-@nL_CDM)+Rtrim(Convert(Char(20),Convert(Int,Right(@cL_LSH,12))+1))
                Where MC=@cL_MC
            End
          Else
            If @nL_Init>1
              Update LSHB set LSH=replace(Convert(Char(10),getdate(),102),'.','')+Replicate('0',@nL_CDT-@nL_CDM)+RTrim(Convert(Char(10),@nL_Init))
                where MC=@cL_MC
            Else
              Update LSHB set LSH=replace(Convert(Char(10),getdate(),102),'.','')+Replicate('0',@nL_CDT-1)+'1'
                where MC=@cL_MC
        End
      If @cL_LX='4'
        begin      -- 前面四位為年 '00' ,年用兩位表示
          Select @nL_CDT=@nL_CD-2
          If left(@cL_LSH,2)=convert(char(2),getdate(),2) /* 年份相等，序號加 1   */
            begin
              Select @nL_CDM=Len(RTrim(Convert(Char(20),Convert(int,Right(@cL_LSH,18))+1)))
       Update LSHB set LSH=Left(@cL_LSH,2)+Replicate('0',@nL_CDT-@nL_CDM)+Rtrim(Convert(Char(20),Convert(int,Right(@cL_LSH,18))+1))
                where MC=@cL_MC
            End
          else
            If @nL_Init>1
               Update LSHB set LSH=replace(Convert(Char(2),getdate(),2),'.','')+Replicate('0',@nL_CDT-@nL_CDM)+Rtrim(Convert(Char(10),@nL_Init))
                 where MC=@cL_MC
            Else
              Update LSHB set LSH=replace(Convert(Char(2),getdate(),2),'.','')+Replicate('0',@nL_CDT-1)+'1'
                where MC=@cL_MC
        End
      If @cL_LX='5'
        begin      -- 前面六位為年月 '0008', 年用兩位
          Select @nL_CDT=@nL_CD-2
          If left(@cL_LSH,2)=convert(char(2),getdate(),2) AND Convert(int,substring(@cL_LSH,3,2))=DATEPART(month,getdate())
            /* 年月相等，序號加 1   */
            begin
              Select @nL_CDM=Len(RTrim(Convert(Char(20),Convert(int,Right(@cL_LSH,16))+1)))
       Update LSHB set LSH=Left(@cL_LSH,4)+Replicate('0',@nL_CDT-@nL_CDM)+Rtrim(Convert(Char(20),Convert(int,Right(@cL_LSH,16))+1))
                where MC=@cL_MC
            End
          else
            If @nL_Init>1
               Update LSHB set LSH=replace(Convert(Char(5),getdate(),2),'.','')+Replicate('0',@nL_CDT-@nL_CDM)+RTrim(Convert(Char(10),@nL_Init))
                  where MC=@cL_MC
            Else
               Update LSHB set LSH=replace(Convert(Char(5),getdate(),2),'.','')+Replicate('0',@nL_CDT-1)+'1'
                  where MC=@cL_MC
        End
      If @cL_LX='6'
        begin   -- 前面六位為年月日 '000910'
          Select @nL_CDT=@nL_CD-6
          If left(@cL_LSH,2)=convert(char(2),getdate(),2) AND Convert(int,substring(@cL_LSH,3,2))=DATEPART(month,getdate())
              AND Convert(Int,SubString(@cL_LSH,5,2))=DatePart(Day,Getdate())
            Begin
              Select @nL_CDM=Len(Rtrim(Convert(Char(20),Convert(Int,Right(@cL_LSH,14))+1)))
              Update LSHB Set LSH=Left(@cL_LSH,6)+Replicate('0',@nL_CDT-@nL_CDM)+Rtrim(Convert(Char(20),Convert(Int,Right(@cL_LSH,14))+1))
                Where MC=@cL_MC
            End
          Else
            If @nL_Init>1
              Update LSHB set LSH=replace(Convert(Char(8),getdate(),2),'.','')+Replicate('0',@nL_CDT-@nL_CDM)+RTrim(Convert(Char(10),@nL_Init))
                where MC=@cL_MC
            Else
              Update LSHB set LSH=replace(Convert(Char(8),getdate(),2),'.','')+Replicate('0',@nL_CDT-1)+'1'
                where MC=@cL_MC
        End
    end
CLOSE LSHB_cursor
DEALLOCATE LSHB_cursor
select LSH from LSHB where MC=@cL_MC
End