MySql syntax error on procedure parameter

Question

I am trying to write a simple procedure but am encountering a syntax error at the first parameter. As best I can tell I'm following the syntax of CREATE PROCEDURE correctly.

I am limited to accessing my database with phpMyAdmin. Here is the create script I'm trying to run:

DROP PROCEDURE IF EXISTS product_index_swap/
CREATE PROCEDURE product_index_swap (@id INT, @oldIndex INT, @newIndex INT)
BEGIN
    DECLARE @swapID;
    SET @swapID = (SELECT `id` FROM `product` WHERE `order_index` = @newIndex LIMIT 1);
    UPDATE `products` SET `order_index` = (CASE WHEN `id` = @id THEN @newIndex
                                                WHEN `id` = @swapID THEN @oldIndex END)
    WHERE `id` IN (@id, @swapID);
END

I am using the option on phpMyAdmin to change the delimiter to /.

I receive a syntax error "near '@id INT, @oldIndex INT....". I thought I may encounter more delimiter errors since I'm not entirely clear on the scope of them. I believe if that was the problem the error would be on a new line in the procedure when it failed to understand a semicolon, not at the parameters declaration.

score 1 · Accepted Answer · answered Dec 08 '17 at 17:10

You're using the Microsoft SQL Server convention of putting @ before all the parameters and local variables. MySQL doesn't do this.

In MySQL syntax, procedure parameters have no sigil.

Also parameters are typically declared IN or OUT or INOUT.

CREATE PROCEDURE product_index_swap (IN id INT, IN oldIndex INT, IN newIndex INT)
BEGIN
    DECLARE swapID;
    ...

MySQL variables that have the @ sigil are session variables.

See also:

score 0 · Answer 2 · answered Dec 08 '17 at 17:17

In MySQL, the @var variables are session level variables.

Use normal variables without the @ and make sure you do not have conflict with column names:

CREATE PROCEDURE product_index_swap (in_id INT, in_oldIndex INT, in_newIndex INT)
BEGIN
    DECLARE v_swapID int;

    SELECT id into v_swapID
    FROM product 
    WHERE order_index = in_newIndex 
    LIMIT 1;

    UPDATE products
    SET order_index = CASE WHEN id = in_id THEN in_newIndex
                           WHEN id = v_swapID THEN in_oldIndex 
                      END
    WHERE id IN (in_id, v_swapID);
END

MySql syntax error on procedure parameter

2 Answers2

Linked