MySQL CREATE PROCEDURE syntax error

Question

I've tried every possible combination I can think of to resolve this error but it keeps happening. Any help appreciated. This is just modifying the sakila sample database to do more complex things with.

See towards bottom I labeled the error with -- HERE!.

USE sakila;
DROP PROCEDURE IF EXISTS sp_randCustMult;
DELIMITER //
CREATE PROCEDURE sp_randCustMult()

BEGIN
/* section of code left out for troubleshooting
    IF EXISTS (SELECT * FROM information_schema.columns WHERE table_name = customer AND column_name = multiplier) 
        THEN ALTER TABLE customer DROP COLUMN multiplier;
    IF EXISTS (SELECT * FROM information_schema.columns WHERE table_name = customer AND column_name = cust_ranking) 
        THEN ALTER TABLE customer DROP COLUMN cust_ranking;

 END IF;
*/
-- add new columns
ALTER TABLE customer
    ADD COLUMN multiplier DECIMAL(3,2) AFTER active;
/* this column not relevant now
ALTER TABLE customer
    ADD COLUMN cust_ranking VARCHAR(10) AFTER multiplier;
*/
    -- declare a counter
    SET @start = (SELECT MIN(customer_id) FROM customer);
    SET @stop  = (SELECT MAX(customer_id) FROM customer);
    -- start while loop
    WHILE @start <= @stop 
            DO
            UPDATE customer
            -- insert multiplier based on random distribution
            SET multiplier =
            (SELECT 
                (CASE 
                    WHEN RAND() <= 0.65 THEN 1.00
                    WHEN RAND() <= 0.90 THEN 0.85
                    WHEN RAND() <= 1.00 THEN 1.05
                END)
            )
            WHERE customer_id = @start;
            -- tick counter one up
            SET @start = @start + 1;
    END WHILE;
-- HERE! syntax error on END before //
END//
DROP PROCEDURE sp_randCustMult//
DELIMITER ;

EDIT1: To clarify, MySql version is:

MySQL Workbench Community (GPL) for Mac OS X version 6.1.4 revision 11773 build 1454

And the error response from Workbench:

Error Code: 1064. You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'END' at line 35

EDIT2: Edited code as suggested. Error no longer happens, however data is not being updated at all. (all NULL in new column)

It seems the problem is elsewhere, I think with RAND() I will post an answer once I figure it out. Thanks all. — Phrancis
– Phrancis, Commented May 24, 2014 at 6:18
Why are you looping? Why not just do UPDATE customer SET multiplier = CASE … END without any WHERE clause? — Alex Trebek
– Alex Trebek, Commented May 24, 2014 at 6:33

Alex Trebek · Accepted Answer · 2014-05-24 05:39:12Z

1

Your CREATE PROCEDURE doesn't match the required syntax as described in the MySQL manual (simplified below by including just the relevant parts):

CREATE
    PROCEDURE sp_name ([proc_parameter[,...]])
    routine_body

routine_body:
    Valid SQL routine statement

The routine_body consists of a valid SQL routine statement. This can be a simple statement such as SELECT or INSERT, or a compound statement written using BEGIN and END. Compound statements can contain declarations, loops, and other control structure statements. The syntax for these statements is described in Section 13.6, “MySQL Compound-Statement Syntax”.

Therefore, this junk…

IF EXISTS (SELECT * FROM information_schema.columns WHERE table_name = customer AND column_name = multiplier) 
    THEN ALTER TABLE customer DROP COLUMN multiplier;
IF EXISTS (SELECT * FROM information_schema.columns WHERE table_name = customer AND column_name = cust_ranking) 
    THEN ALTER TABLE customer DROP COLUMN cust_ranking;
END IF;

… is illegal. Perhaps you meant to move it into the BEGIN … END compound statement?

You also need a semicolon after END WHILE.

edited May 24, 2014 at 5:39

answered May 24, 2014 at 5:15

Alex Trebek

9176 silver badges13 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Phrancis Over a year ago

Error still happens after moving IF EXISTS statements into the BEGIN ... END statement...

Phrancis Over a year ago

It also happens if I comment out the whole IF EXISTS statement, evidently the problem must be elsewhere :(

Aman Aggarwal · Accepted Answer · 2014-05-24 05:34:17Z

0

All functional logic must be between the tags BEGIN and END

So the IF conditions and alter query stuff must lie between BEGIN and END tags of procedure..

Thanks

answered May 24, 2014 at 5:34

Aman Aggarwal

18.6k10 gold badges55 silver badges84 bronze badges

1 Comment

Phrancis Over a year ago

Modified code, no longer getting an error but the script does nothing. See edits to OP, thank you.

Community · Accepted Answer · 2017-04-13 12:40:36Z

0

I figured out the issues, here is the solution on CR: https://codereview.stackexchange.com/questions/51603/mysql-modifying-sakila-database

edited Apr 13, 2017 at 12:40

CommunityBot

11 silver badge

answered May 24, 2014 at 8:13

Phrancis

2,2902 gold badges29 silver badges41 bronze badges

Collectives™ on Stack Overflow

MySQL CREATE PROCEDURE syntax error

3 Answers 3

2 Comments

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

2 Comments

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related