[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'.