STORED PROCEDURE OR TRIGGERS? CALCULATE FEES

Question

Post reply

STORED PROCEDURE OR TRIGGERS? CALCULATE FEES

bamek_daddy

SSC Enthusiast

Points: 133
More actions
March 13, 2011 at 6:49 pm

#145109

HELLO!
I have two tables : product , tax
Product'fields: id amount Tax
1 150 ?
2 300 ?
TAX fields : Range_start ; Range_end ; TAX_Amount
Range_start Range_end Tax_Amount
100 200 10
201 300 25
301 400 30
For amount of 150 fees is 10
for amount of 201 fees is 25
help to write a trigger or stored procedure to select and insert tax_amount in product table when amount is inserted .
Thanks.

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

ColdCoffee SSC-Dedicated Points: 39972 More actions · Answer 1

THis is the proc:

IF OBJECT_ID('CalculateTax','P') IS NOT NULL

DROP PROCEDURE CalculateTax

GO

CREATE PROCEDURE CalculateTax

AS

BEGIN

UPDATE P

SET P.Tax = T.Tax_Amount

FROM Products P

JOIN Tax T

ON p.amount BETWEEN T.range_start AND T.range_end

END

damek_daddy, tel me honestly, isn't this part of your course homework?:w00t:

Jeff Moden SSC Guru Points: 1004339 More actions · Answer 2

Don't forget to update only where the Tax column is NULL so previous rows at possibly different tax rates aren't updated.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

chris.stuart SSCarpal Tunnel Points: 4965 More actions · Answer 3

ColdCoffee (3/13/2011)
THis is the proc:
UPDATE P
SET P.Tax = T.Tax_Amount
FROM Products P
JOIN Tax T
ON p.amount BETWEEN T.range_start AND T.range_end
END

Just a question on the bold part, I've actually never seen a join like this before using a between. Is this efficient to join like this, it just seems that it might be a bit slow for the join to use between. :unsure:

tfifield SSCrazy Eights Points: 9655 More actions · Answer 4

Chris,

Using BETWEEN as a join condition is fairly common. It's really the same as using >= AND <= in either the join or WHERE clause. The efficiency depends on the same type of criteria as any other JOIN or WHERE clause - indexes and so forth.

Todd Fifield

chris.stuart SSCarpal Tunnel Points: 4965 More actions · Answer 5

tfifield (3/15/2011)
Chris,
Using BETWEEN as a join condition is fairly common. It's really the same as using >= AND <= in either the join or WHERE clause. The efficiency depends on the same type of criteria as any other JOIN or WHERE clause - indexes and so forth.
Todd Fifield

Never to old to learn!:-D

Been in It for 21 years, with about 10 years with SQL, can think that I've seen it, and have never used it.

Thanks:-)