×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Contact US

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Function Returning Rowcounts

Function Returning Rowcounts

Function Returning Rowcounts

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

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.

RE: Function Returning Rowcounts

(OP)
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.

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login


Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close