经典存储过程2
CREATE PROCEDURE WG_GGSXGF_addForSHGF_WW_No
@SQBH int,
@YHDLM varchar(50),
@THLY nvarchar(300)=null,
@type varchar(1),
@PMSX varchar(30)=null,
@XLSX varchar(30)=null,
@ZLSX varchar(30)=null,
@YZJXGSJ datetime
AS
declare @SQDM varchar(8)
declare @ZL varchar(35)
declare @XL varchar(35)
declare @PM varchar(35)
declare @GF nvarchar(200)
declare @MB varchar(15)
declare @JLDW nvarchar(4)
declare @SX1 nvarchar(50)
declare @SX2 nvarchar(50)
declare @SX3 nvarchar(50)
declare @SX4 nvarchar(50)
declare @SX5 nvarchar(50)
declare @SX6 nvarchar(50)
declare @SX7 nvarchar(50)
declare @SX8 nvarchar(50)
declare @sign_ZL varchar(1)
declare @sign_XL varchar(1)
declare @ZLLSH_MAX int
declare @ZLcurNo varchar(2)
declare @XLLSH_MAX int
declare @XLcurNo varchar(2)
declare @PMLSH_MAX int
declare @PMcurNo varchar(2)
declare @XM varchar(10)
declare @row1 int,@row2 int,@row3 int,@row4 int,@row int,@rowc int
declare @BJ varchar(35) --标记中类小类名称的汉字是否有重复
declare @temp varchar(50)
declare @ZJXGSJ datetime
declare @empty_no varchar(2)
declare @curno_next varchar(8)
declare @curno_34 varchar(2)
declare @curno_56 varchar(2)
declare @curno_78 varchar(2)
set @row1=0
set @row2 =0
set @row3=0
set @row4 =0
set @rowc=0
set @BJ=null
if(@type='1')
begin
select @ZJXGSJ=ZJXGSJ from WG_SQGF where SQBH=@SQBH
if(datediff(ss,@ZJXGSJ,@YZJXGSJ)=0)
begin
select @SQDM=SQGFDM,@ZL=SQZL,@XL=SQXL,@PM=substring(SQPM,5,50),@GF=SQGF,@MB=SQMB,@JLDW=SQJLDW,@SX1=SX1,@SX2=SX2,@SX3=SX3,@SX4=SX4,@SX5=SX5,@SX6=SX6,@SX7=SX7,@SX8=SX8 from WG_SQGF where SQBH=@SQBH
set @sign_ZL=substring(@ZL,1,1)
if(@sign_ZL='|')
begin
set @ZL=substring(@ZL,5,50)
end
set @sign_XL=substring(@XL,1,1)
if(@sign_XL='|')
begin
set @XL=substring(@XL,5,50)
end
set @XM=(select XM from WG_YHML where YHDLM=@YHDLM)
if(@sign_ZL='|') --申请了中类小类名称时形成流水号
begin
set @empty_no='01'
--set @flag=0
declare zxl_lsh cursor for
select distinct substring(DM,1,4) from WG_GGSXGF where len(DM)>=4 and substring(DM,1,2)= substring(@SQDM,1,2) order by substring(DM,1,4)
open zxl_lsh
--fetch next from zxl_lsh into @curno_prev
fetch next from zxl_lsh into @curno_next
set @curno_34=substring(@curno_next,3,2)
while (@@fetch_status=0 and cast(@curno_34 as int)<=cast(@empty_no as int))
begin
if(cast(@curno_34 as int)=cast(@empty_no as int))
set @empty_no=@empty_no+1
if(cast(@empty_no as int)<10)
set @empty_no='0'+cast(cast(@empty_no as int) as varchar(1))
fetch next from zxl_lsh into @curno_next
set @curno_34=substring(@curno_next,3,2)
end
close zxl_lsh
deallocate zxl_lsh
set @SQDM=substring(@SQDM,1,2)+@empty_no+'0101'
end
else
if(@sign_XL='|') ----申请了小类名称时形成流水号
begin
set @empty_no='01'
--set @flag=0
declare zxl_lsh_XL cursor for
select distinct substring(DM,1,6) from WG_GGSXGF where len(DM)>=6 and substring(DM,1,4)= substring(@SQDM,1,4) order by substring(DM,1,6)
open zxl_lsh_XL
--fetch next from zxl_lsh into @curno_prev
fetch next from zxl_lsh_XL into @curno_next
set @curno_56=substring(@curno_next,5,2)
while (@@fetch_status=0 and cast(@curno_56 as int)<=cast(@empty_no as int))
begin
if(cast(@curno_56 as int)=cast(@empty_no as int))
set @empty_no=@empty_no+1
if(cast(@empty_no as int)<10)
set @empty_no='0'+cast(cast(@empty_no as int) as varchar(1))
fetch next from zxl_lsh_XL into @curno_next
set @curno_56=substring(@curno_next,5,2)
end
close zxl_lsh_XL
deallocate zxl_lsh_XL
set @SQDM=substring(@SQDM,1,4)+@empty_no+'01'
end
else --申请了名称时形成流水号
begin
set @empty_no='01'
--set @flag=0
declare zxl_lsh_PM cursor for
select distinct substring(DM,1,8) from WG_GGSXGF where len(DM)>=8 and substring(DM,1,6)= substring(@SQDM,1,6) order by substring(DM,1,8)
open zxl_lsh_PM
--fetch next from zxl_lsh into @curno_prev
fetch next from zxl_lsh_PM into @curno_next
set @curno_78=substring(@curno_next,7,2)
while (@@fetch_status=0 and cast(@curno_78 as int)<=cast(@empty_no as int))
begin
if(cast(@curno_78 as int)=cast(@empty_no as int))
set @empty_no=@empty_no+1
if(cast(@empty_no as int)<10)
set @empty_no='0'+cast(cast(@empty_no as int) as varchar(1))
fetch next from zxl_lsh_PM into @curno_next
set @curno_78=substring(@curno_next,7,2)
end
close zxl_lsh_PM
deallocate zxl_lsh_PM
set @SQDM=substring(@SQDM,1,6)+@empty_no
end
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
--查找中类小类品名汉字是否有重复的
if(@sign_ZL='|')
begin
select @temp=WZMC from WG_GGSXGF where substring(DM,1,2)=substring(@SQDM,1,2) and len(DM)=4 and ltrim(rtrim(WZMC))=ltrim(rtrim(@ZL))
if(@temp<>null)
set @BJ='中类名重复'
end
else
if(@sign_XL='|')
begin
select @temp=WZMC from WG_GGSXGF where substring(DM,1,4)=substring(@SQDM,1,4) and len(DM)=6 and ltrim(rtrim(WZMC))=ltrim(rtrim(@XL))
if(@temp<>null)
set @BJ='小类名重复'
end
else
begin
select @temp=WZMC from WG_GGSXGF where substring(DM,1,6)=substring(@SQDM,1,6) and len(DM)=8 and ltrim(rtrim(WZMC))=ltrim(rtrim(@PM))
if(@temp<>null)
set @BJ='品名重复'
end
--print(@XLcurNo)
--print(@ZLcurNo)
--print(@PMcurNo)
--print(@SQDM)
--print(@temp)
if(@BJ=null)
begin
update WG_SQGF set SHGFDM=@SQDM,SHJLDW=@JLDW,SHR=@XM,SHRQ=getdate(),SHGFBZ='P',THLY='审核无误通过',ZJXGSJ=getdate() where SQBH=@SQBH
select @row1=@@rowcount
insert into WG_GGSXGF(DM,WZMC,MCSX,MSGF,GFMB,SX1,SX2,SX3,SX4,SX5,SX6,SX7,SX8,PP,JLDW)
values(
@SQDM,
@PM,
@PMSX,
@GF,
@MB,
@SX1,
@SX2,
@SX3,
@SX4,
@SX5,
@SX6,
@SX7,
@SX8,
null,
@JLDW
)
select @row2=@@rowcount
if(@sign_ZL='|')
begin
insert into WG_GGSXGF(DM,WZMC,MCSX,MSGF,GFMB,SX1,SX2,SX3,SX4,SX5,SX6,SX7,SX8,PP,JLDW)
values(substring(@SQDM,1,4),@ZL,@ZLSX,null,null,null,null,null,null,null,null,null,null,null,null)
select @row3=@@rowcount
end
if(@sign_XL='|')
begin
insert into WG_GGSXGF(DM,WZMC,MCSX,MSGF,GFMB,SX1,SX2,SX3,SX4,SX5,SX6,SX7,SX8,PP,JLDW)
values(substring(@SQDM,1,6),@XL,@XLSX,null,null,null,null,null,null,null,null,null,null,null,null)
select @row4=@@rowcount
end
set @row = @row1 + @row2 +@row3 + @row4
if(@sign_ZL='|' and @sign_XL='|')
begin
if(@row=4)
set @rowc = 1
else
set @rowc=0
end
else
if((@sign_ZL='|' and @sign_XL<>'|') or (@sign_ZL<>'|' and @sign_XL='|'))
begin
if(@row=3)
set @rowc=1
else
set @rowc=0
end
else
begin
if(@row=2)
set @rowc = 1
else
set @rowc =0
end
select @rowc
end
else
begin
set @rowc=4
select @rowc,@BJ
print('OK')
end
end
else --读出数据的时间和审核提交时的时间不一致时
begin
set @rowc=-1
set @BJ='SJ'
select @rowc,@BJ
end
end
if(@type='2')
begin
select @ZJXGSJ=ZJXGSJ from wg_SQGF where SQBH=@SQBH
if(datediff(ss,@ZJXGSJ,@YZJXGSJ)=0)
begin
set @XM=(select XM from WG_YHML where YHDLM=@YHDLM)
update WG_SQGF set SHR=@XM,SHRQ=getdate(),SHGFBZ='P',THLY=@THLY,ZJXGSJ=getdate() where SQBH=@SQBH
select(@@rowcount)
end
else
begin
set @row1=-1
select(@row1)
end
end
GO
浙公网安备 33010602011771号