Function Returning Rowcounts

[briangriffin]

I don't write a ton of functions, but this one has me stumped.

-- =============================================
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.

 

[briangriffin]

Never mind... the function was fine, in my haste I tested with just

rptContractInvoiceLineTax (1000,10001,1)

instead of

select rptContractInvoiceLineTax(1000,10001,1)

I wish the first one had given me an error instead of the rowcounts.