briangriffin
Programmer
I don't write a ton of functions, but this one has me stumped.
The code outside the function works fine, but the function returns the rowcounts for inserting into the variable tables. Probably something simple, but no idea why it's doing that. TIA.
Code:
-- =============================================
alter FUNCTION rptContractInvoiceLineTax
(
@InvoiceLineAmount money,
@TaxAreaID smallint,
@TaxGroupID smallint
)
RETURNS money
AS
BEGIN
--set NOCOUNT off
DECLARE @LineTaxAmount money
declare @TaxMatrix table
(TaxAreaID smallint,
TaxGroupID smallint,
TaxType smallint,
TaxID1 smallint,
TaxID2 smallint,
TaxID3 smallint,
TaxID4 smallint,
TaxID5 smallint,
TaxID6 smallint,
TaxID7 smallint,
TaxID8 smallint,
TaxID9 smallint,
TaxID10 smallint)
insert into @TaxMatrix
select
TM.TaxAreaID,
TM.TaxGroupID,
TM.TaxType,
TM.TaxID1,
TM.TaxID2,
TM.TaxID3,
TM.TaxID4,
TM.TaxID5,
TM.TaxID6,
TM.TaxID7,
TM.TaxID8,
TM.TaxID9,
TM.TaxID10
from
TaxMatrix TM
declare @TaxRates table
(TaxAreaID smallint,
TaxGroupID smallint,
TaxType smallint,
TaxID smallint,
TaxRateField varchar(12),
TaxCode varchar(12),
TaxName varchar(50),
TaxRate numeric(5,2))
insert into @TaxRates
select
UP.*,
Tax.TaxCode,
Tax.Name,
coalesce(TR.Rate,0) as Rate
from @TaxMatrix
UNPIVOT
(
TaxID FOR TaxIDs in (TaxID1,TaxID2,TaxID3,TaxID4,TaxID5,TaxID6,TaxID7,TaxID8,TaxID9,TaxID10)
)
as UP left outer join Tax
on UP.TaxID = Tax.TaxID
and Tax.Deleted = 0
left outer join TaxRate TR
on Tax.TaxID = TR.TaxID
set @LineTaxAmount =
(select sum(@InvoiceLineAmount * (TaxRate * .01))
from @TaxRates
where TaxAreaID = @TaxAreaID
and TaxGroupID = @TaxGroupID)
return @LineTaxAmount
END
GO
The code outside the function works fine, but the function returns the rowcounts for inserting into the variable tables. Probably something simple, but no idea why it's doing that. TIA.