Stored Procedure in MYSQL

[kavinmehta]

Hi,


I an a newbie in MYSQL and I have to write a stored prcedure. Below is my code. It is not working and am getting an error 1064 in the all these lines.

Would appreciate ur help.

CREATE PROCEDURE add_purchases (
IN P_buyer_id INT,
IN P_item_id INT,
IN P_num_items INT )
DECLARE v_num INT DEFAULT 0,
DECLARE v_num_sold INT DEFAULT 0 ,
DECLARE v_cost FLOAT (7,2) default 0.00,
DECLARE v_total FLOAT (7,2) default 0.00 ,
DECLARE v_tax_rate FLOAT (4,4) default 0.0000,
DECLARE v_buyer_state CHAR(2) default "OH";
-- This Procedure will add a new purchase record into the PURCHASE Table
-- 1- First make sure number of items requested are available in the ITEMS_FOR_SALE table
-- update the appropriate fields
-- 2- Insert a new purchase record with buyer_id passed as a first parameter
-- 3- Diplay an error message if not enough Items are available
-- 4- Set default tax rate to OH State
BEGIN
-- Get Buyers State From Table
SELECT state into v_buyer_state
FROM BUYERS
WHERE buyer_id = P_buyer_id;
-- Check if number of Items requested in available in ITEMS_FOR_SALE table
SELECT sale_price, num_sold into v_num, v_cost,v_num_sold
FROM items_for_sale
WHERE item_id = P_item_id;
If (v_num_sold >= P_num_itmes) then
-- Update Items_for_sale record to display new Num_sold as a result of the purchase
SET v_num_sold = v_num_sold + P_num_itms;


thanks,
kavin mehta

 

[r937]

SELECT sale_price, num_sold into v_num, v_cost,v_num_sold

you can't select 2 columns into 3 variables

r937.com | rudy.ca