StoredProcedure--Sum(TableA) to Update Amount in TableB

Question

User-1497429768 posted

Hi,

   The StoredProcedure is as follows-I want to sum from TableA into TableB

update TableB set amount=A.AMOUNT
from (select sum(IsNull(amount,0)) as amount from TableA where no=@no) A
where no=@no

It can work fine.But if the TableA is empty,the StoredProcedure  can't work. so,how to fix that. Thanks.

jeff

Answer 1

User-1509636757 posted

But if the TableA is empty,the StoredProcedure  can't work. so,how to fix that.

do you face any error when TableA is empty? I try to generate similar scenario with table variable and I do not see any issue when TableA is empty:

declare @TableB table (amount decimal, no int)
declare @TableA table (amount decimal, no int)
declare @no int = 1

--insert into @TableA select 100,1
--insert into @TableA select 200,1
--insert into @TableB select null,1

update @TableB set amount=A.AMOUNT
from (select sum(IsNull(amount,0)) as amount from @TableA where no=@no) A
where no=@no

select * from @TableB

Answer 2

User-1497429768 posted

Hi kaushalparik27,

   When I delete TableA,then the TableA is empty.In the meantime,after running the StoredProcedure,I found the result of the TableB is the same value(not zero). So something is not correct.

   Hope can give me any suggestion.Thanks.

 jeff

    

Answer 3

User77042963 posted

Merge TableB tgt
Using(select no, Isnull(sum(amount),0) as amount from TableA 
where no=@no Group by no) src On tgt.no=src.no
When Matched then 
update
set amount=src.AMOUNT ;

Answer 4

User-1497429768 posted

Hi limno,

   It is very helpful to me.But I still encounter one problem--If the TableA is empty,I hope the amount of field in TableB is zero. I try to modify your code as follows:

Merge TableB B
Using (select no,sum(IsNull(amount,0)) as amount from TableA where no=@NO group by no) A on A.no=B.no
when Matched then update set B.amount=A.AMOUNT
when not Matched then update set B.amount=0;

A syntax error is thrown. Have any idea? I research all day and can't find any solution.

  Thank you for your help.

jeff

Answer 5

User-1497429768 posted

Hi limno,

 I use another method,and it work. So Thanks again.

IF exists(select no from TableA where no=@NO) begin
update TableB set amount=A.AMOUNT
from (select sum(IsNull(amount,0)) as amount from TableA where no=@NO) A
where no=@NO
end
else begin
update TableB set amount=0 where no=@NO
end

best regards,

jeff