mySQL workbench syntax error for OUTPUT in stored procedure

240 views Asked by At

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);

0

There are 0 answers