Here's what i did
Declare @PreviousAmount money
set @PreviousAmount = 0
select @PreviousAmount = @PreviousAmount + Inp.AmountPaid
From Invoice as Inv , InvoicePayment as Inp
where Inv.InvoiceId = Inp.InvoiceId
and I got a error saying "Must declare the scalar variable "@PreviousAmount"."
Updated - This is actual code which gives error
CREATE PROCEDURE [dbo].[spInvoiceTsk]
@AmountPaid money
AS
BEGIN
SET NOCOUNT ON;
Declare @PreviousAmount money
set @PreviousAmount = 0
select case
when (@AmountPaid = Inv.InvoiceAmount )
Then @AmountPaid
else (
select @PreviousAmount = @PreviousAmount + Inp.AmountPaid
From dbo.Invoice as Inv , dbo.InvoicePayment as Inp
where Inv.InvoiceId = Inp.InvoiceId
)
from dbo.Invoice as Inv
--select *
--from Invoice as a
--Inner Join InvoicePayment as IP ON a.InvoiceId = IP.InvoiceId
update I
set I.AmountPaid = @PreviousAmount
from Invoice as I , InvoicePayment as IP
where I.AmountPaid = IP.AmountPaid
END
GO