sql-server - 想要根据两个 id 更新列，它们有两个百分比

Question

这是我的存储过程。在这里，我得到两个百分比的 id 值。我想根据与该 id 具有百分比的 id 列更新 acc_cashbook 中的列金额。但在这里它仅基于一个 id 进行更新。我想根据两个 id 更新到他们的特定列。请帮我。

ALTER procedure [dbo].[refdocedit]
(@RefDocID int, 
@LabNo int)
as
begin

declare @U_labNo int
declare @TestOrPkg int
declare @ID int
declare @StdAmt int
declare @Percentage decimal
declare @NameOfParty varchar(50)
declare @Voucher_NameOfParty varchar(50)
declare @Amount decimal

select @U_labNo=U_labNo from LabRegDetail where LabNo=@LabNo

select @TestOrPkg=TestOrPkg,@ID=ID,@StdAmt=StdAmt from LabPatientTestDetails where U_labNo=@U_labNo

select @Percentage=Percentage from RefDocLabCommission where RefDocID=@RefDocID and ID=@ID

update LabRegDetail set RefDocId=@RefDocID where LabNo=@LabNo



select NameOfParty,Voucher_NameOfParty,Amount from Acc_CashBook  where LabNo=@LabNo and ID=@ID
and Ns_Or_PHM=@TestOrPkg 



set @Amount=(select distinct (@Percentage/100)*@StdAmt from RefDocLabCommission rdlc,LabRegDetail lrd,LabTestMaster ltm,LabPatientTestDetails lptd,Acc_CashBook acc
where lrd.LabNo=acc.LabNo and lptd.U_labNo=lrd.U_labNo and lrd.RefDocId=rdlc.RefDocID and rdlc.ID=ltm.TestID  
and rdlc.ID=acc.ID and lrd.LabNo=@LabNo and acc.ID=@ID)

set @NameOfParty=(select  RefFirstName+''+RefMiddleName+''+RefLastName from RefDocMaster where  RefDocID=@RefDocID)


update Acc_CashBook set NameOfParty=@NameOfParty,Voucher_NameOfParty=@NameOfParty
 where LabNo=@LabNo and Voucher_PartyType=2
--and Ns_Or_PHM=@TestOrPkg and ID=@ID


update Acc_CashBook set Amount=@Amount where Voucher_PartyType=2 and LabNo=@LabNo 


update Acc_BankBook set NameOfParty=@NameOfParty,Voucher_NameOfParty=@NameOfParty
 where LabNo=@LabNo and Voucher_PartyType=2
--and Ns_Or_PHM=@TestOrPkg and ID=@ID


update Acc_BankBook set Amount=@Amount where Voucher_PartyType=2 and LabNo=@LabNo 



end

标签： sql-server

Answer 1

我认为问题在于你在程序上思考 - 你将如何用简单的代码而不是基于集合来做到这一点。我已经根据我对模式的假设尽可能地重新编码了它。看看这个。它现在只剩下几个基于集合的更新。

还假设仍然需要选择查询，并且它可能会从连接返回多行。如果不是，请删除不同的，或完全删除查询。

ALTER procedure [dbo].[refdocedit]
(@RefDocID int, 
@LabNo int)
as
begin

declare @NameOfParty varchar(50)

update LabRegDetail set RefDocId=@RefDocID where LabNo=@LabNo

select @NameOfParty= RefFirstName+''+RefMiddleName+''+RefLastName from RefDocMaster where  RefDocID=@RefDocID

select distinct acc.NameOfParty,acc.Voucher_NameOfParty,acc.Amount 
from LabRegDetail lrd
join LabPatientTestDetails lptd on lptd.U_labNo=lrd.U_labNo 
join Acc_CashBook acc on acc.ID=lptd.ID and acc.Ns_Or_PHM=lptd.TestOrPkg
where lrd.LabNo=@LabNo 

update acc set NameOfParty=@NameofParty, Voucher_NameOfParty=@NameOfParty,
    Amount=(rdlc.Percentage/100)*lptd.StdAmt
from LabRegDetail lrd
join LabPatientTestDetails lptd on lptd.U_labNo=lrd.U_labNo 
join Acc_CashBook acc on acc.ID=lptd.ID and acc.Voucher_PartyType=2
join LabRegDetail lrd on lrd.LabNo=acc.LabNo
join RefDocLabCommission rdlc on lrd.RefDocId=rdlc.RefDocID and rdlc.ID=acc.ID
where lrd.LabNo=@LabNo 

update bb set NameOfParty=@NameOfParty,Voucher_NameOfParty=@NameOfParty,
    Amount=(rdlc.Percentage/100)*lptd.StdAmt
from LabRegDetail lrd
join LabPatientTestDetails lptd on lptd.U_labNo=lrd.U_labNo 
join Acc_CashBook acc on acc.ID=lptd.ID and acc.Voucher_PartyType=2
join LabRegDetail lrd on lrd.LabNo=acc.LabNo
join RefDocLabCommission rdlc on lrd.RefDocId=rdlc.RefDocID and rdlc.ID=acc.ID
join Acc_BankBook b on bb.LabNo=lrd.LabNo and bb.Voucher_PartyType=2
where lrd.LabNo=@LabNo 

end