Avatar of Eileen Murphy
Eileen Murphy
Flag for United States of America asked on

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'.
Microsoft SQL Server

Avatar of undefined
Last Comment
Eileen Murphy

8/22/2022 - Mon
SOLUTION
Scott Pletcher

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
GET A PERSONALIZED SOLUTION
Ask your own question & get feedback from real experts
Find out why thousands trust the EE community with their toughest problems.
ASKER CERTIFIED SOLUTION
Eileen Murphy

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
GET A PERSONALIZED SOLUTION
Ask your own question & get feedback from real experts
Find out why thousands trust the EE community with their toughest problems.
Eileen Murphy

ASKER
It worked!
Experts Exchange is like having an extremely knowledgeable team sitting and waiting for your call. Couldn't do my job half as well as I do without it!
James Murphy