Stored Procedure Newbie needs help!

[b]Trying to recalculate invoice items to update a parent table with totals -- not my design (ugh)

Here's my attempt at creating a stored procedure:



USE [Data1]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE UpdateInvoiceHeader
      
      @InvoiceID int

AS
BEGIN
      
      DECLARE @InvLineItems FLOAT
      DECLARE @InvTotal FLOAT
      DECLARE @InvPayments FLOAT
      DECLARE @InvPOF FLOAT
      DECLARE @InvTaxPct FLOAT
      DECLARE @InvTaxAmt FLOAT
      DECLARE @InvBalanceDue FLOAT
      DECLARE @InvMgrDiscount FLOAT
      DECLARE @InvShipping FLOAT
      
      SET @InvLineItems = (SELECT Isnull(SUM(ISNULL([Qty],1)*[Price]) ,0) FROM InvoiceLines WHERE invoiceID = @InvoiceID)
      
      SET @InvPayments = (SELECT Isnull(SUM([PmtAmt]) ,0) FROM DepositLines WHERE invoiceID = @InvoiceID)
      
      SET @InvPOF = (SELECT Isnull(SUM([PmtAmt],0) FROM DepositLines WHERE InvID = @InvoiceID)
            
      UPDATE InvoiceHdr
      SET
      
            InvSubTotal            = @InvLineItems,
      
            Pmts                  = @InvPayments
            
            SET @InvTaxPct      = Isnull(TaxPercentage,0)      
            
            if NOT ISNULL(@InvTaxPct)
                  begin
                        SET @InvTaxAmt      = ROUND(convert(real,@InvLineItems) * Convert(real,(@InvTaxPct) / 100),2)
                  end
            else if
                  begin
                        SET @InvTaxAmt      =      0
                  end
            
            SET @InvMgrDiscount      = Isnull(MgrDisc,0)
            
            SET @InvShipping      = Isnull(Shipping,0)
            
            SET @InvTotal            = @InvLineItems + @InvTaxAmt + @InvShipping
            
            SET @InvBalanceDue      = @InvTotal - @InvPayments - @InvMgrDiscount
      
            TaxAmt                  = @InvTaxAmt
            BalanceDue            = @InvBalanceDue
      
      Where InvoiceID            = @InvoiceID      

END


Here are the errors I'm getting: There are far fewer than when I began :)


Msg 174, Level 15, State 1, Procedure UpdateInvoiceHeader, Line 24
The SUM function requires 1 argument(s).
Msg 174, Level 15, State 1, Procedure UpdateInvoiceHeader, Line 35
The isnull function requires 2 argument(s).
Msg 156, Level 15, State 1, Procedure UpdateInvoiceHeader, Line 39
Incorrect syntax near the keyword 'else'.
Msg 102, Level 15, State 1, Procedure UpdateInvoiceHeader, Line 52
Incorrect syntax near 'TaxAmt'.
Eileen MurphyIndependent Application DeveloperAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Scott PletcherSenior DBACommented:
I can't tell where some of the values come from, but this should get you headed in the right direction (and a better direction.  For example, rather than load values into variables, you can get them using OUTER APPLY, as shown below.  This is much more efficient and, ultimately, much easier to write and maintain.


CREATE PROCEDURE UpdateInvoiceHeader
     
      @InvoiceID int

AS
SET NOCOUNT ON;            

UPDATE IH
SET      
    InvSubTotal            = ISNULL(InvLineItems,0),
    Pmts                        = ISNULL(InvPayments,0),
    InvTaxPct                = CASE WHEN TaxPercentage > 0 THEN ROUND(convert(real,ISNULL(InvLineItems,0)) * Convert(real,(ISNULL(InvTaxPct,0)) / 100),2) ELSE 0 END,
    InvMgrDiscount     = ISNULL(MgrDisc,0),
    InvShipping            = ISNULL(Shipping,0)

    /* ?? I'm not sure where some of these values come from
    SET @InvTotal            = @InvLineItems + @InvTaxAmt + @InvShipping
           
    SET @InvBalanceDue      = @InvTotal - @InvPayments - @InvMgrDiscount
     
    TaxAmt                  = @InvTaxAmt
    BalanceDue            = @InvBalanceDue
    */

FROM dbo.InvoiceHdr IH
OUTER APPLY (
    SELECT SUM(ISNULL(IL.[Qty],1)*IL.[Price]) AS InvLineItems
    FROM dbo.InvoiceLines IL
    WHERE IL.InvoiceID = IH.InvoiceID
) AS LineItemsTotal
OUTER APPLY (
    SELECT SUM(DL.[PmtAmt]) AS InvPayments
    FROM dbo.DepositLines DL
    WHERE DL.InvoiceID = IH.InvoiceID
) AS PaymentsTotal
OUTER APPLY (
    SELECT SUM(DL.[PmtAmt]) AS InvPOF
    FROM dbo.DepositLines DL
    WHERE DL.InvID = IH.InvoiceID
) AS POFTotal
     
Where IH.InvoiceID         = @InvoiceID      
 
/*End of proc*/        
GO
Eileen MurphyIndependent Application DeveloperAuthor Commented:
OK - I kept fooling with it and here's what I got to work (so far since now I have to test it with live data) ---->>>
---------------------------------------------------------------------------------------------------------------------------------------------------------------
USE [Data1]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE UpdateInvoiceHeader
      
      @InvoiceID int

AS
SET NOCOUNT ON;
BEGIN
      
      DECLARE @InvLineItems MONEY
      DECLARE @InvPayments MONEY
      DECLARE @InvTaxAmt MONEY
      DECLARE @InvTaxPct FLOAT
      DECLARE @InvTotal MONEY

      SET @InvLineItems = (SELECT SUM(InvoiceLines.[Qty]*InvoiceLines.[Price]) FROM InvoiceLines WHERE invoiceID = @InvoiceID)
      
      SET @InvPayments = (SELECT SUM(DepositLines.[PmtAmt]) FROM DepositLines WHERE invoiceID = @InvoiceID)
      
      UPDATE InvoiceHdr
      SET
            @InvTaxPct = Isnull(InvoiceHdr.TaxPercentage,0),
            @InvTaxAmt = CASE WHEN @InvTaxPct > 0 THEN ROUND(@InvLineItems * (@InvTaxPct / 100) ,2)ELSE 0 END,
            InvSubTotal      = @InvLineItems,
            TaxAmt = @InvTaxAmt,
            Pmts = @InvPayments,
            @InvTotal = @InvLineItems + @InvTaxAmt + isnull(InvoiceHdr.Shipping,0),
            InvTotal = @InvTotal,
            BalanceDue      = @InvTotal - @InvPayments - isnull(InvoiceHdr.MgrDisc,0)
      Where InvoiceID      = @InvoiceID      

END

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Eileen MurphyIndependent Application DeveloperAuthor Commented:
It worked!
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.