Must declare scalar variable "@PreviousAmount"

Question

Here is 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 the error message Msgstr "Must declare scalar variable" @PreviousAmount "."

Updated - This is the actual code that gives the 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

Answer 1

There are no problems in your request, I believe that you leave a declaration during the execution of the request, so try to run all at once. Change JOINin the following format:

DECLARE @PreviousAmount MONEY   
SET @PreviousAmount = 0

SELECT @PreviousAmount  = @PreviousAmount  + Inp.AmountPaid
FROM Invoice AS Inv
INNER JOIN InvoicePayment AS Inp ON Inv.InvoiceId = Inp.InvoiceId

Answer 2

I did not have a problem, just look at this example.

Declare @PreviousAmount money   
set @PreviousAmount = 0

declare @invoice table(InvoiceId int, InvoiceDate datetime )
declare @InvoicePayment table(InvoiceId int, AmountPaid money )

insert into @invoice values (1, getdate()-365), (2, getdate()-30), (3, getdate()-3), (4, getdate()-1)
insert into @InvoicePayment values (1, 5) , (2, 30), (3, 3), (4, 100)

select @PreviousAmount  = @PreviousAmount  + Inp.AmountPaid
    From @Invoice as Inv , @InvoicePayment as Inp
    where Inv.InvoiceId = Inp.InvoiceId

select @PreviousAmount

--To verify the above answer
select sum(Inp.AmountPaid) totalamountOfAllInvoice
    From @Invoice as Inv , @InvoicePayment as Inp
    where Inv.InvoiceId = Inp.InvoiceId

Both queries give 138 (5 + 30 + 3 + 100)

Updated Answer

According to your updated question, here are 2 things

• select .

. , :

Declare @PreviousAmount money   
set @PreviousAmount = 0

declare @invoice table(InvoiceId int, InvoiceDate datetime, InvoiceAmount int )
declare @InvoicePayment table(InvoiceId int, AmountPaid money )

insert into @invoice values (1, getdate()-365 , 100), (2, getdate()-30 , 200), (3, getdate()-3 , 300), (4, getdate()-1 , 400)
insert into @InvoicePayment values (1, 50) , (1, 20), (2, 200), (3, 50), (3, 100)


Declare  @AmountPaid money = 10, @invoiceid int = 1

Set @PreviousAmount =
    (Select 
        Case 
            When (@AmountPaid = Inv.InvoiceAmount ) Then @AmountPaid 
            Else (
                select sum( Inp.AmountPaid) 
                From 
                @InvoicePayment as Inp where Inv.InvoiceId = Inp.InvoiceId              
            )
            end amountpaid
        from @Invoice as Inv 
        where inv.invoiceid = @invoiceid        
    )

select @previousamount

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

    /*Rather than above query, use left outer join and sum it to get previous amount of an invoice*/
    Set @PreviousAmount =
    (Select 
        Case 
            When (@AmountPaid = Inv.InvoiceAmount ) Then @AmountPaid 
            Else (
                select sum( Inp.AmountPaid) 
                From 
                @InvoicePayment as Inp where Inv.InvoiceId = Inp.InvoiceId              
            )
            end amountpaid
        from @Invoice as Inv 
        where inv.invoiceid = @invoiceid        
    )

    update I
    set I.AmountPaid = @PreviousAmount
    from Invoice as I , InvoicePayment as IP
    where I.AmountPaid = IP.AmountPaid



END
GO

Answer 3

Do not find problems with your request. Provide a few details or try the query below.

Declare @PreviousAmount money   
set @PreviousAmount = 0

set @PreviousAmount  = (Select @PreviousAmount  + Inp.AmountPaid
    From Invoice as Inv , InvoicePayment as Inp
    where Inv.InvoiceId = Inp.InvoiceId)

Select @PreviousAmount