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 L [1] [2] 下一页 没有相关教程
|