Stored procedure not returning any value when it should(MySQL)

Question

I have this stored procedure that I want it to return a value(the lastinsertid). All it does return is 0. My question is what is wrong with code below?

CREATE PROCEDURE my_stored_procedure(
    IN inCartId int
)
BEGIN

    DECLARE orderId INT;

    -- Insert a new record into orders and obtain the new order ID
    INSERT INTO orders (created_on) VALUES (NOW());

    -- Obtain the new Order ID
    SELECT LAST_INSERT_ID() INTO orderId;

    -- Insert order details in order_detail table
    INSERT INTO order_detail (
        order_id, product_id, attributes, product_name, quantity, unit_cost
    )
    SELECT
        orderId,
        p.id,
        sc.attributes,
        p.name,
        sc.quantity,
        COALESCE( NULLIF( p.discounted_price, 0 ), p.price ) AS unit_cost
    FROM
        shopping_cart sc
        INNER JOIN products p ON sc.product_id = p.id
    WHERE
        sc.cart_id = inCartId
        AND
        sc.buy_now;

    -- Save the order's total amount
    UPDATE
        orders
    SET
        total_amount = (
            SELECT
                SUM( unit_cost * quantity )
            FROM
                order_detail
            WHERE
                order_id = orderId
        )
    WHERE
        id = orderId;

    -- Clear the shopping cart
    CALL shopping_cart_empty( inCartId );

END

Answer 1

• In most database systems like MySQL/MariaDB and MS SQL Server, Stored Procedures do not have "return values" in the same way that functions do.

• Instead, stored procedures return data to their caller through Output Parameters.

  • In MySQL, a FUNCTION does return a value _{but a FUNCTION is different from a Stored Procedure and the explaining differences is best done in separate question}.

• (In Microsoft SQL Server and Sybase, Stored Procedures do all return a single int "return code" value, however this value is an indication of status (like how executable programs have an exit-code) and is not meant to be used to return data from your database.)

• To use Output Parameters in MySQL and MariaDB use the OUT keyword instead of the IN keyword and then assign to that parameter.

In your case, replace the local variable orderId with the OUT newOrderId parameter I've added in the stored procedure parameters list. No other changes are necessary.

CREATE PROCEDURE my_stored_procedure(
    IN inCartId int,
    OUT newOrderId int
)
BEGIN

    -- Insert a new record into orders and obtain the new order ID
    INSERT INTO orders (created_on) VALUES (NOW());

    -- Obtain the new Order ID
    SELECT LAST_INSERT_ID() INTO newOrderId;

    -- Insert order details in order_detail table
    INSERT INTO order_detail (
        order_id, product_id, attributes, product_name, quantity, unit_cost
    )
    SELECT
        orderId,
        p.id,
        sc.attributes,
        p.name,
        sc.quantity,
        COALESCE( NULLIF( p.discounted_price, 0 ), p.price ) AS unit_cost
    FROM
        shopping_cart sc
        INNER JOIN products p ON sc.product_id = p.id
    WHERE
        sc.cart_id = inCartId
        AND
        sc.buy_now;

    -- Save the order's total amount
    UPDATE
        orders
    SET
        total_amount = (
            SELECT
                SUM( unit_cost * quantity )
            FROM
                order_detail
            WHERE
                order_id = orderId
        )
    WHERE
        id = orderId;

    -- Clear the shopping cart
    CALL shopping_cart_empty(inCartId);

END