# Help with some SQL Procedueres

Discussion in 'OT Technology' started by r1dd1ck, Apr 9, 2008.

1. ### r1dd1ckNew Member

Joined:
Nov 8, 2007
Messages:
32
0
I am trying to learn SQL and my book does not do a good job explaining how to write procedures.

I have this information:

Code:
```[FONT=Courier New][FONT=Courier New]CREATE TABLE bb_Tax ([/FONT]
[FONT=Courier New]     idState NUMBER(2),[/FONT]
[FONT=Courier New]     State char(2),[/FONT]
[FONT=Courier New]     TaxRate NUMBER(4,3),[/FONT]
[FONT=Courier New]     CONSTRAINT tax_idstate_pk PRIMARY KEY(idState) );[/FONT]

[FONT=Courier New]INSERT INTO bb_tax VALUES(1,'VA',.045);[/FONT]
[FONT=Courier New]INSERT INTO bb_tax VALUES(2,'NC',.03);[/FONT]
[FONT=Courier New]INSERT INTO bb_tax VALUES(3,'SC',.06);[/FONT]
[/FONT]```

I need to write a procedure to do the tax calculations. The state and subtotal should be inputs, and the amount of tax (subtotal * taxrate) should be the output. I am suposed to use the attribute type bb_tax.state for the state parameter.

Any help would be much appriciated.

2. ### pepdogNew Member

Joined:
Nov 21, 2005
Messages:
4,211
0
Location:
seattle
You wanna do something like:

create procedure CalculateTax
(
@State char(2),
@SubTotal NUMBER(4,3),
@Total NUMBER(4,3) OUTPUT
)
as
select @Total = (@SubTotal * bb_tax.TaxRate)
from bb_tax
where bb_tax.State = @State

declare @Total NUMBER(4,3)
exec CalculateTax 'VA', 100, @Total output

Last edited: Apr 9, 2008