Trying to add a GetTaxAmount Stored Procedure

attipa · July 18th, 2003, 12:18 PM

Hello,

Thanks in advance for whoever can help me. The following code is a stored procedure I am trying to get to work to add tax for CA residents. I can't get it working. Can someone please help?

-------------------------------------------
ALTER PROCEDURE GetTaxAmount
(@CartID varchar(50),
@CustomerID varchar(50))
AS

Declare @Amount Money
Declare @Tax Money
Declare @State varchar

Select @Amount = SUM(Product.[Price]*ShoppingCart.Quantity)
From ShoppingCart
INNER JOIN Product
ON ShoppingCart.ProductID = Product.ProductID
WHERE ShoppingCart.CartID = @CartID

Select @State = Customer.State
From Customer
Where CustomerID = @CustomerID

Select @Tax = @Amount*0.0825

If @State = CA
    Return @Tax
Else
    Return 0

    Return

Imar · July 18th, 2003, 12:31 PM

Hi there,

What error do you get? What exactly is the problem?

One thing that I can see is that CA is not enclosed in quotes. Change the last part to this:

Code:

If @State = 'CA'
    Return @Tax
Else
    Return 0

If that doesn't fix it, please be more specific.

Regards,

Imar

---------------------------------------
Imar Spaanjaars
Everyone is unique, except for me.

attipa · July 18th, 2003, 02:56 PM

thanks for the help. i did that and it compiled. however, it is always returning 0. what can i do?

Imar · July 18th, 2003, 03:12 PM

I think you'll need to change the code that does the state lookup to this:

SELECT @State = (SELECT Customer.State From Customer Where CustomerID = @CustomerID)

You may also need to check if this statement returns a value. Maybe there is no customer with an ID of @CustomerID.....

Cheers,

Imar

---------------------------------------
Imar Spaanjaars
Everyone is unique, except for me.