MySQL stored Proc syntax confusion

Question

Using syntax form docs, read many examples and docs, I have reduced code down to isolate the syntax that is causing the failure, I cannot see it... I'm still getting an error.

Running thru MySQL command line

PARAMS: A_score smallint, B_score smallint

delimiter $$
create procedure my_procedure(A_score smallint, B_score smallint)
begin
    DECLARE  winner BIGINT DEFAULT 0;
    DECLARE  winningScore, losingScore SMALLINT DEFAULT;
    if A_score > B_score then
        SET winningScore = 1;
    elseif A_score < B_score then
        SET winningScore = 2;
    end if;
    start transaction;
        UPDATE
            winners
        SET
            winner = winningScore 
        WHERE
            id = 1
    commit;
end $$
delimiter ;

ERROR 1064 (42000): 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 '; if A_score > B_score then SET winningScore = 1; elseif A_score' at line 4

excellent Barranka.. that got me further down the code... Im going to have to edit my question now... — j-p
– j-p, Commented Sep 7, 2015 at 22:38
Happy to help. By the way, if you find my answer below useful, upvote it (and/or accept it if it solves your issue) — Barranka
– Barranka, Commented Sep 7, 2015 at 22:39
Did you put the end delimiter after the last END (In my example below, I set the delimiter to $$ before the procedure, and that's what must be written after the closing END) — Barranka
– Barranka, Commented Sep 7, 2015 at 22:44

Barranka · Accepted Answer · 2015-09-07 22:46:38Z

1

I see you forgot to write SET (in the previous version of your question) to assign values to your variables.

(I changed the type for winningScore and losingScore to be characters, because smallints can't be strings):

-- Be sure to change the default delimiter before writing your procedure
delimiter $$
create procedure my_procedure(A_score smallint, B_score smallint)
begin
    DECLARE  winner BIGINT DEFAULT 0;
    DECLARE  winningScore, losingScore VARCHAR(2) DEFAULT;
    if A_score > B_score then
        SET winningScore = 'A';
    --  ^^^--you forgot this
    elseif A_score < B_score then
        SET winningScore = 'B';
    --  ^^^--and this
    else
        SET winningScore = 'AB';
    --  ^^^--and this
    end if;
    start transaction;
    -- Do whatever your transaction is meant to be
    commit;
end $$
--  ^^--- And I think you're forgetting to put this

-- Be sure to reset the delimiter to ; after you end your procedure
delimiter ;

Quoting from the reference manual:

Variables can be set directly with the SET statement. See Section 13.7.4, “SET Syntax”.

Hope this helps

edited Sep 7, 2015 at 22:46

answered Sep 7, 2015 at 22:21

Barranka

21.1k14 gold badges72 silver badges89 bronze badges

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

5 Comments

j-p Over a year ago

ya - I tried that, I'll change the sample to reflect that - same issues

Barranka Over a year ago

@jpmyob please edit your question and put the full code of your procedure... What are you doing in the places where your comments are?

Barranka Over a year ago

@jpmyob You forgot to put SET to set the value of your variables (by the way, I meant edit your question... I've already done that)

j-p Over a year ago

ya - see that makes perfect sense to me, however as mentioned in the earlier version of the question, I'm using a DB IDE, Navicat 8, and the "full" syntax of delimiter, create... etc, is taken over by the interface, I get to start with 'begin' and end with 'end' - if i try to declare delimiters, or 'create' specifically, it bombs... I'll try this thru another means, it appears as though my syntax is correct... just that the tool is being a bit of a jerk... Thx - will give you a vote up in a few min...

Barranka Over a year ago

@jpmyob I've never used Navicat, so I'm not familiar with it. However, you can try to do this directly with the MySQL command line interface (or you can use MySQL Workbench); if it works, then you'll know that Navicat is indeed behaving like a jerk.

Collectives™ on Stack Overflow

MySQL stored Proc syntax confusion

1 Answer 1

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related