ITPub博客

首页 > 应用开发 > IT综合 > 自动编号的存储过程 (转)

自动编号的存储过程 (转)

原创 IT综合 作者:amyz 时间:2007-11-02 15:59:50 0 删除 编辑
自动编号的存储过程 (转)[@more@]

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 Mailto:MC=@cL_MC">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 

GO


来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/10752019/viewspace-980539/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论
  • 博文量
    3984
  • 访问量
    7334685