Help with some SQL Procedueres

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

  1. r1dd1ck

    r1dd1ck New Member

    Joined:
    Nov 8, 2007
    Messages:
    32
    Likes Received:
    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. pepdog

    pepdog New Member

    Joined:
    Nov 21, 2005
    Messages:
    4,211
    Likes Received:
    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

Share This Page