I have declared a variable called order_id that I want to store the auto-incremented id set for the order I try to insert. I get a syntax error at OUTPUT.
I have read many examples and what I see, this is the correct way to code it. All the code is inside of a stored procedure. Inserting an order and rest of the procedure works, except I need the auto-incremented id for inserting into another table.
CREATE DEFINER=`name`@`%` PROCEDURE `conduct_order`(
IN username_par VARCHAR(50),
IN addr_par VARCHAR(100),
IN zip_par VARCHAR(45))
BEGIN
DECLARE user_addr VARCHAR(100);
DECLARE user_zip VARCHAR(45);
DECLARE user_country VARCHAR(100);
DECLARE user_phone VARCHAR(45);
DECLARE user_email VARCHAR(45);
DECLARE this_date VARCHAR(45);
DECLARE temp INT;
SET user_id = (SELECT id FROM customers WHERE username= username_par);
SET user_addr = addr_par;
SET user_zip = zip_par;
SET user_country = (SELECT country FROM customers WHERE username=username_par);
SET user_phone = (SELECT phone FROM customers WHERE username=username_par);
SET user_email = (SELECT email FROM customers WHERE username=username_par);
SET this_date = (SELECT CURDATE());
INSERT INTO orders(
customer_id,
customer_addr,
customer_phone,
customer_email,
order_date,
order_state,
customer_postalcode,
customer_country)
OUTPUT order_id INTO temp
VALUES(
user_id,
user_addr,
user_phone,
user_email,
this_date,
'not sent',
user_zip,
user_country);
INSERT INTO order_items(order_id, product_id, amount)
VALUES(
(SELECT id FROM orders
WHERE customer_id=user_id
AND order_date=this_date
AND customer_addr=user_addr
AND customer_postalcode=user_zip
AND customer_country=user_country),
(SELECT product_id FROM carts WHERE customer_id=user_id),
(SELECT amount FROM carts WHERE customer_id=user_id));
END
MySQL workbench says
Syntax error: unexpected 'output' (identifier)
I have no clue what is wrong.
EDIT: I found another question on stackoverflow with a similar problem that was solved by using a table variable, but I can't seem to declare a table variable. Wordbench says
TABLE not a valid input at this time.
This is when I try `DECLARE MyTableVar TABLE (IdentityColumn BIGINT);