Must declare the scalar variable "@PreviousAmount"

Question

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

Are you trying to create a `cumulative sum of AmountPaid` ???? — Praveen, May 15 '17 at 06:03
Personally I get 'incorrect syntax near =` when I run your stored procedure creation script. Your SP is a long way from functioning. The middle select isn't valid. Are you trying to allocate a part or full payment from a payment table? You can probably do that with one or two updates — Nick.Mc, May 15 '17 at 10:43
@melina, I update your question with your answer. so please delete your answer. and also check my answer. — Ajay2707, May 15 '17 at 10:44
ok . Thaank you and will try your code if that doesn't work will ask again — Melina Sharma, May 15 '17 at 10:57

score 1 · Answer 1 · answered May 15 '17 at 06:13

There isn't any issue in your query, my wild guess is that you are leaving declaration part while executing the query so please try to run all at once. Please change the JOIN in below 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

Ajay2707 · Answer 2 · 2017-05-15T11:21:38.100

I did not get any issue, just see 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 gives 138 (5 + 30 + 3 + 100)

Updated answer

As per your updated question, here are 2 things

First you have to also need invoice id to pass in your procedure
you can not use parameter in select with multiple result.

Take a look this example. This is above table redesign with sample data to understand :

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

Now update this procedure based on that

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

I just say you, that I did not get any error which you get. for that sample code is paste with result — Ajay2707, May 15 '17 at 08:59
Can you try the above post that I posted? It's the full code and there's an error — Melina Sharma, May 15 '17 at 09:16
you said "First you have to also need invoice id to pass in your procedure" I cannot pass InvoiceId as parameter because I have a salesTransaction table and what i require is to Tag the InvoiceId in SalesTransaction table after the Invoice table is calculated — Melina Sharma, May 16 '17 at 09:17
I mean to say, that you need previousamount, so my question is on what you get previousamount, against all invoice or on particular invoice. — Ajay2707, May 16 '17 at 11:05
[link](http://stackoverflow.com/questions/44019230/output-inserted-or-deleted-in-sql-server) can you look at this @Ajay2707 ? — Melina Sharma, May 17 '17 at 08:19

score 0 · Answer 3 · answered May 15 '17 at 06:09

Don't find any issue with your query. Provide some more details or try to execute the below query.

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

Must declare the scalar variable "@PreviousAmount"

3 Answers3