SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
/*
全部更新 Customer 中 Nonr 标志,依据 InVoice 中的 公式:
原币交易总额(LocAmount) + 原币税额(LocTaxAmount) - 原币进货折让(LocAllowance) - 原币冲帐额(LocTradeOff) - 原币冲帐折让(LocCashAllowance) <> 0
Xing Mo Su 2001/04/06
k07 2002/01/23 lock<>3 特殊处理 lock=5 modi 48 llf
加上退票(trncode:6006)冲帐的处理2002-01-30
*/ALTER PROCEDURE BKD_Stk_UPnonr
@CompanyID varchar(200),
@TrnDate smalldatetime ,
@macno varchar(3),
@staffno utstaffno
AS
declare @groupCusNo varchar(100)
declare @CtrlCusNo varchar(100)
declare @sumResult UTMoney
declare @sumAdvance UTMoney
declare @sumYing UTMoney
declare @tempNonr UTMoney
declare @DocNO UTDocNo
--declare @staffno utstaffno --冲帐员工号
--declare @macno varchar(3) --机台名称
begin
if @CompanyID is Null return -10
begin tran
--一.先处理特殊情况如赠品或折让等於小计等
if @staffno ='' and @macno=''
Declare Cur_Inv Cursor For
SELECT docno from Invoice
where (COMPANY = @CompanyID) and (IFPOST = 1) and (ifvalid = 1) and (lock not in ('3','5')) and trncode in('2201','1102','6006') and (convert(varchar(10),PostDate,111) <= @TrnDate)
and grossamount+taxamount-allowance-tradeoff-cashallowance =0
order by DocNo
else
begin
if @staffno<>'' and @macno<>''
Declare Cur_Inv Cursor For
SELECT docno from Invoice
where (COMPANY = @CompanyID) and (IFPOST = 1) and (ifvalid = 1) and (lock not in ('3','5')) and trncode in('2201','1102','6006') and (convert(varchar(10),PostDate,111) <= @TrnDate)
and grossamount+taxamount-allowance-tradeoff-cashallowance =0 and saleman_no=@staffno and macno=@macno
order by DocNo
else
begin
if @staffno<>''
Declare Cur_Inv Cursor For
SELECT docno from Invoice
where (COMPANY = @CompanyID) and (IFPOST = 1) and (ifvalid = 1) and (lock not in ('3','5')) and trncode in('2201','1102','6006') and (convert(varchar(10),PostDate,111) <= @TrnDate)
and grossamount+taxamount-allowance-tradeoff-cashallowance =0 and saleman_no=@staffno
order by DocNo
else
Declare Cur_Inv Cursor For
SELECT docno from Invoice
where (COMPANY = @CompanyID) and (IFPOST = 1) and (ifvalid = 1) and (lock not in ('3','5')) and trncode in('2201','1102','6006') and (convert(varchar(10),PostDate,111) <= @TrnDate)
and grossamount+taxamount-allowance-tradeoff-cashallowance =0 and macno=@macno
order by DocNo
end
end
Open cur_Inv
Fetch cur_Inv Into @DocNo
While @@fetch_status=0
begin
update invoice set lock=5 where (COMPANY = @CompanyID) and (DocNo=@DocNo)
fetch next from cur_Inv into @DocNo
end
close cur_Inv
deallocate cur_Inv
Update Customer Set Nonr = 0,ar = 0,Advance= 0 Where Company = @CompanyID if @staffno ='' and @macno=''
declare rsSumInvoice cursor for
Select CusNo, sum(case trncode when '2201' then (grossamount+taxamount-allowance-tradeoff-cashallowance) when '6006' then (grossamount+taxamount-allowance-tradeoff-cashallowance) else (grossamount+taxamount-allowance-tradeoff-cashallowance)*-1 end) AS Result
From Invoice where (IFPOST = 1) and (ifvalid = 1) and (lock not in ('3','5')) and trncode in('2201','1102','6006') and (COMPANY = @CompanyID) and (convert(varchar(10),PostDate,111) <= @TrnDate) group by CusNo
else
begin
if @staffno<>'' and @macno<>''
declare rsSumInvoice cursor for
Select CusNo, sum(case trncode when '2201' then (grossamount+taxamount-allowance-tradeoff-cashallowance) when '6006' then (grossamount+taxamount-allowance-tradeoff-cashallowance) else (grossamount+taxamount-allowance-tradeoff-cashallowance)*-1 end) AS Result
From Invoice where (IFPOST = 1) and (ifvalid = 1) and (lock not in ('3','5')) and trncode in('2201','1102','6006') and (COMPANY = @CompanyID) and (convert(varchar(10),PostDate,111) <= @TrnDate) and saleman_no=@staffno and macno=@macno group by CusNo
else
begin
if @staffno<>''
declare rsSumInvoice cursor for
Select CusNo, sum(case trncode when '2201' then (grossamount+taxamount-allowance-tradeoff-cashallowance) when '6006' then (grossamount+taxamount-allowance-tradeoff-cashallowance) else (grossamount+taxamount-allowance-tradeoff-cashallowance)*-1 end) AS Result
From Invoice where (IFPOST = 1) and (ifvalid = 1) and (lock not in ('3','5')) and trncode in('2201','1102','6006') and (COMPANY = @CompanyID) and (convert(varchar(10),PostDate,111) <= @TrnDate) and saleman_no=@staffno group by CusNo
else
declare rsSumInvoice cursor for
Select CusNo, sum(case trncode when '2201' then (grossamount+taxamount-allowance-tradeoff-cashallowance) when '6006' then (grossamount+taxamount-allowance-tradeoff-cashallowance) else (grossamount+taxamount-allowance-tradeoff-cashallowance)*-1 end) AS Result
From Invoice where (IFPOST = 1) and (ifvalid = 1) and (lock not in ('3','5')) and trncode in('2201','1102','6006') and (COMPANY = @CompanyID) and (convert(varchar(10),PostDate,111) <= @TrnDate) and macno=@macno group by CusNo
end
end open rsSumInvoice
fetch rsSumInvoice into @groupCusNo, @sumResult
while @@fetch_status=0
begin if @sumResult <> 0
begin
/* 求本位币预收款写到customer中*/ if @staffno ='' and @macno=''
Select @sumAdvance = (select sum(localAmt-localTrade) AS Result From Advance where (IFPOST = 1) and (ifvalid = 1) and (COMPANY = @CompanyID) and (convert(varchar(10),PostDate,111) <= @TrnDate) and localAmt-localTrade<>0 and cusno=@groupCusNo group by CusNo)
else
begin
if @staffno<>'' and @macno<>''
Select @sumAdvance = (select sum(localAmt-localTrade) AS Result From Advance where (IFPOST = 1) and (ifvalid = 1) and (COMPANY = @CompanyID) and (convert(varchar(10),PostDate,111) <= @TrnDate) and localAmt-localTrade<>0 and cusno=@groupCusNo and saleman_no=@staffno and macno=@macno group by CusNo)
else
begin
if @staffno<>''
Select @sumAdvance = (select sum(localAmt-localTrade) AS Result From Advance where (IFPOST = 1) and (ifvalid = 1) and (COMPANY = @CompanyID) and (convert(varchar(10),PostDate,111) <= @TrnDate) and localAmt-localTrade<>0 and cusno=@groupCusNo and saleman_no=@staffno group by CusNo)
else
Select @sumAdvance = (select sum(localAmt-localTrade) AS Result From Advance where (IFPOST = 1) and (ifvalid = 1) and (COMPANY = @CompanyID) and (convert(varchar(10),PostDate,111) <= @TrnDate) and localAmt-localTrade<>0 and cusno=@groupCusNo and macno=@macno group by CusNo)
end
end -- print @sumAdvance
/*到指定日期为止的本位币应收帐款*/
if @staffno ='' and @macno=''
select @sumYing = (select sum(case trncode when '2201' then (localAmt+localTax-localAlo-locTradeoff-locCashallowance) when '6006' then (localAmt+localTax-localAlo-locTradeoff-locCashallowance) else (localAmt+localTax-localAlo-locTradeoff-locCashallowance)*-1 end) from invoice where (IFPOST = 1) and (ifvalid = 1) and trncode in('2201','1102','6006')and (COMPANY = @CompanyID) and (convert(varchar(10),PostDate,111) <= @TrnDate) and cusno=@groupCusNo group by CusNo)每当服务器执行这个存储过程的时候,就会造成所有客户端卡机, 经过查看,发现SQL阻塞严重。
初步猜测是这个存储过程把客户信息等全用X锁锁住了,造成其它客户端不能访问客户信息,无法执行下一步!
因为我是SQL菜鸟,所以还请高手赐教,不胜感激!
GO
SET ANSI_NULLS ON
GO
/*
全部更新 Customer 中 Nonr 标志,依据 InVoice 中的 公式:
原币交易总额(LocAmount) + 原币税额(LocTaxAmount) - 原币进货折让(LocAllowance) - 原币冲帐额(LocTradeOff) - 原币冲帐折让(LocCashAllowance) <> 0
Xing Mo Su 2001/04/06
k07 2002/01/23 lock<>3 特殊处理 lock=5 modi 48 llf
加上退票(trncode:6006)冲帐的处理2002-01-30
*/ALTER PROCEDURE BKD_Stk_UPnonr
@CompanyID varchar(200),
@TrnDate smalldatetime ,
@macno varchar(3),
@staffno utstaffno
AS
declare @groupCusNo varchar(100)
declare @CtrlCusNo varchar(100)
declare @sumResult UTMoney
declare @sumAdvance UTMoney
declare @sumYing UTMoney
declare @tempNonr UTMoney
declare @DocNO UTDocNo
--declare @staffno utstaffno --冲帐员工号
--declare @macno varchar(3) --机台名称
begin
if @CompanyID is Null return -10
begin tran
--一.先处理特殊情况如赠品或折让等於小计等
if @staffno ='' and @macno=''
Declare Cur_Inv Cursor For
SELECT docno from Invoice
where (COMPANY = @CompanyID) and (IFPOST = 1) and (ifvalid = 1) and (lock not in ('3','5')) and trncode in('2201','1102','6006') and (convert(varchar(10),PostDate,111) <= @TrnDate)
and grossamount+taxamount-allowance-tradeoff-cashallowance =0
order by DocNo
else
begin
if @staffno<>'' and @macno<>''
Declare Cur_Inv Cursor For
SELECT docno from Invoice
where (COMPANY = @CompanyID) and (IFPOST = 1) and (ifvalid = 1) and (lock not in ('3','5')) and trncode in('2201','1102','6006') and (convert(varchar(10),PostDate,111) <= @TrnDate)
and grossamount+taxamount-allowance-tradeoff-cashallowance =0 and saleman_no=@staffno and macno=@macno
order by DocNo
else
begin
if @staffno<>''
Declare Cur_Inv Cursor For
SELECT docno from Invoice
where (COMPANY = @CompanyID) and (IFPOST = 1) and (ifvalid = 1) and (lock not in ('3','5')) and trncode in('2201','1102','6006') and (convert(varchar(10),PostDate,111) <= @TrnDate)
and grossamount+taxamount-allowance-tradeoff-cashallowance =0 and saleman_no=@staffno
order by DocNo
else
Declare Cur_Inv Cursor For
SELECT docno from Invoice
where (COMPANY = @CompanyID) and (IFPOST = 1) and (ifvalid = 1) and (lock not in ('3','5')) and trncode in('2201','1102','6006') and (convert(varchar(10),PostDate,111) <= @TrnDate)
and grossamount+taxamount-allowance-tradeoff-cashallowance =0 and macno=@macno
order by DocNo
end
end
Open cur_Inv
Fetch cur_Inv Into @DocNo
While @@fetch_status=0
begin
update invoice set lock=5 where (COMPANY = @CompanyID) and (DocNo=@DocNo)
fetch next from cur_Inv into @DocNo
end
close cur_Inv
deallocate cur_Inv
Update Customer Set Nonr = 0,ar = 0,Advance= 0 Where Company = @CompanyID if @staffno ='' and @macno=''
declare rsSumInvoice cursor for
Select CusNo, sum(case trncode when '2201' then (grossamount+taxamount-allowance-tradeoff-cashallowance) when '6006' then (grossamount+taxamount-allowance-tradeoff-cashallowance) else (grossamount+taxamount-allowance-tradeoff-cashallowance)*-1 end) AS Result
From Invoice where (IFPOST = 1) and (ifvalid = 1) and (lock not in ('3','5')) and trncode in('2201','1102','6006') and (COMPANY = @CompanyID) and (convert(varchar(10),PostDate,111) <= @TrnDate) group by CusNo
else
begin
if @staffno<>'' and @macno<>''
declare rsSumInvoice cursor for
Select CusNo, sum(case trncode when '2201' then (grossamount+taxamount-allowance-tradeoff-cashallowance) when '6006' then (grossamount+taxamount-allowance-tradeoff-cashallowance) else (grossamount+taxamount-allowance-tradeoff-cashallowance)*-1 end) AS Result
From Invoice where (IFPOST = 1) and (ifvalid = 1) and (lock not in ('3','5')) and trncode in('2201','1102','6006') and (COMPANY = @CompanyID) and (convert(varchar(10),PostDate,111) <= @TrnDate) and saleman_no=@staffno and macno=@macno group by CusNo
else
begin
if @staffno<>''
declare rsSumInvoice cursor for
Select CusNo, sum(case trncode when '2201' then (grossamount+taxamount-allowance-tradeoff-cashallowance) when '6006' then (grossamount+taxamount-allowance-tradeoff-cashallowance) else (grossamount+taxamount-allowance-tradeoff-cashallowance)*-1 end) AS Result
From Invoice where (IFPOST = 1) and (ifvalid = 1) and (lock not in ('3','5')) and trncode in('2201','1102','6006') and (COMPANY = @CompanyID) and (convert(varchar(10),PostDate,111) <= @TrnDate) and saleman_no=@staffno group by CusNo
else
declare rsSumInvoice cursor for
Select CusNo, sum(case trncode when '2201' then (grossamount+taxamount-allowance-tradeoff-cashallowance) when '6006' then (grossamount+taxamount-allowance-tradeoff-cashallowance) else (grossamount+taxamount-allowance-tradeoff-cashallowance)*-1 end) AS Result
From Invoice where (IFPOST = 1) and (ifvalid = 1) and (lock not in ('3','5')) and trncode in('2201','1102','6006') and (COMPANY = @CompanyID) and (convert(varchar(10),PostDate,111) <= @TrnDate) and macno=@macno group by CusNo
end
end open rsSumInvoice
fetch rsSumInvoice into @groupCusNo, @sumResult
while @@fetch_status=0
begin if @sumResult <> 0
begin
/* 求本位币预收款写到customer中*/ if @staffno ='' and @macno=''
Select @sumAdvance = (select sum(localAmt-localTrade) AS Result From Advance where (IFPOST = 1) and (ifvalid = 1) and (COMPANY = @CompanyID) and (convert(varchar(10),PostDate,111) <= @TrnDate) and localAmt-localTrade<>0 and cusno=@groupCusNo group by CusNo)
else
begin
if @staffno<>'' and @macno<>''
Select @sumAdvance = (select sum(localAmt-localTrade) AS Result From Advance where (IFPOST = 1) and (ifvalid = 1) and (COMPANY = @CompanyID) and (convert(varchar(10),PostDate,111) <= @TrnDate) and localAmt-localTrade<>0 and cusno=@groupCusNo and saleman_no=@staffno and macno=@macno group by CusNo)
else
begin
if @staffno<>''
Select @sumAdvance = (select sum(localAmt-localTrade) AS Result From Advance where (IFPOST = 1) and (ifvalid = 1) and (COMPANY = @CompanyID) and (convert(varchar(10),PostDate,111) <= @TrnDate) and localAmt-localTrade<>0 and cusno=@groupCusNo and saleman_no=@staffno group by CusNo)
else
Select @sumAdvance = (select sum(localAmt-localTrade) AS Result From Advance where (IFPOST = 1) and (ifvalid = 1) and (COMPANY = @CompanyID) and (convert(varchar(10),PostDate,111) <= @TrnDate) and localAmt-localTrade<>0 and cusno=@groupCusNo and macno=@macno group by CusNo)
end
end -- print @sumAdvance
/*到指定日期为止的本位币应收帐款*/
if @staffno ='' and @macno=''
select @sumYing = (select sum(case trncode when '2201' then (localAmt+localTax-localAlo-locTradeoff-locCashallowance) when '6006' then (localAmt+localTax-localAlo-locTradeoff-locCashallowance) else (localAmt+localTax-localAlo-locTradeoff-locCashallowance)*-1 end) from invoice where (IFPOST = 1) and (ifvalid = 1) and trncode in('2201','1102','6006')and (COMPANY = @CompanyID) and (convert(varchar(10),PostDate,111) <= @TrnDate) and cusno=@groupCusNo group by CusNo)每当服务器执行这个存储过程的时候,就会造成所有客户端卡机, 经过查看,发现SQL阻塞严重。
初步猜测是这个存储过程把客户信息等全用X锁锁住了,造成其它客户端不能访问客户信息,无法执行下一步!
因为我是SQL菜鸟,所以还请高手赐教,不胜感激!
解决方案 »
免费领取超大流量手机卡,每月29元包185G流量+100分钟通话, 中国电信官方发货