troubleshooting Question

Stored Procedure Newbie needs help!

Avatar of Eileen Murphy
Eileen MurphyFlag for United States of America asked on
Microsoft SQL Server
3 Comments2 Solutions80 ViewsLast Modified:
[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'.
ASKER CERTIFIED SOLUTION
Eileen Murphy
Independent Application Developer

Our community of experts have been thoroughly vetted for their expertise and industry experience.

Join our community to see this answer!
Unlock 2 Answers and 3 Comments.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 2 Answers and 3 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros