MYSQL Stored Procedures: Variable Declaration and Conditional Statements

Question

I have looked over numerous tutorials, manuals and documentations, but I still can not get this to work.

I am trying to create a stored procedure using phpMyAdmin.

I cant seem to find the errors here, the sql errors are so vague...

CREATE PROCEDURE insertToonOneShot(IN locale CHAR(2), IN name VARCHAR(16), IN realm VARCHAR(24), IN faction CHAR(1), IN toon_level INT, IN class_name INT)
BEGIN
DECLARE @realmID INT;
DECLARE @classID INT;
DECLARE @toonID INT;
SET @realmID = SELECT id FROM realms WHERE realms.name = realm;
SET @classID = SELECT id FROM classes WHERE classes.name = class_name;
IF NOT @realmID IS NULL AND NOT @classID IS NULL AND @toonID IS NULL THEN
INSERT INTO 
toon (`locale`, `name`, `realm_id`, `faction`, `level`, `class_id`)
VALUES
(locale, name, @realmID, faction, toon_level, @classID);
END IF;
END;

The error I am getting right now is:

#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 @realmID INT; DECLARE @classID INT; DECLARE @toonID INT; SET @rea at line 3

Probably one of the more frustrating things I have ever had to do...

I have seen many tutorials online that show using the @ symbol in variable declaration, and others not using it, I have even seen some that use VAR instead of DECLARE. What is the right syntax?...

miken32 · Answer 1 · 2022-11-03T22:55:35.710

This question, and its answers, seem to be confusing session variables (which are prefixed with @) with procedural variables (which are not prefixed.) See the answers to this question for more information.

The accepted solution resolves the error by using session variables, but it could introduce problems related to variable scope. For example, if a variable called realmID has been defined outside the procedure, its value will be overwritten when the procedure is run.

The correct way to resolve the problem is to use only procedural variables. These are DECLAREd at the start of the procedure without the @ prefix.

DELIMITER $$

CREATE PROCEDURE insertToonOneShot(
    IN locale CHAR(2),
    IN name VARCHAR(16),
    IN realm VARCHAR(24),
    IN faction CHAR(1),
    IN toon_level INT,
    IN class_name INT
)
BEGIN
    DECLARE realmID INT;
    DECLARE classID INT;

    SELECT id INTO realmID FROM realms WHERE realms.name = realm LIMIT 1;
    SELECT id INTO classID FROM classes WHERE classes.name = class_name LIMIT 1;
    IF realmID IS NOT NULL AND classID IS NOT NULL THEN
        INSERT INTO toon (`locale`, `name`, `realm_id`, `faction`, `level`, `class_id`)
        VALUES (locale, name, realmID, faction, toon_level, classID);
    END IF;
END$$

DELIMITER ;

score 12 · Answer 2 · answered Sep 16 '13 at 15:49

12

When you have a subquery, it needs to have parentheses. These lines:

SET @realmID = SELECT id FROM realms WHERE realms.name = realm;
SET @classID = SELECT id FROM classes WHERE classes.name = class_name;

Should be:

SET @realmID = (SELECT id FROM realms WHERE realms.name = realm);
SET @classID = (SELECT id FROM classes WHERE classes.name = class_name);

Or, better yet, you don't need the set:

SELECT @realmID := id FROM realms WHERE realms.name = realm;
SELECT @classID := id FROM classes WHERE classes.name = class_name;

answered Sep 16 '13 at 15:49

Gordon Linoff

1,242,037
58
646
786

I implemented the `@realmID := id...` method. Still receiving the same error on line 3. It does not seem to like the DECLARE statements. – Vigs Sep 16 '13 at 18:09
@Gordon is that we don't need to declare the data type? – Avinash Raj Jun 23 '15 at 06:48
2

is that we need to use `@` symbol? In my sp, there isn't an `@` while declaration. – Avinash Raj Jun 23 '15 at 06:50
@AvinashRaj . . . The OP used session variables in the question, so this answer also uses them. You can read about the difference here: https://dev.mysql.com/doc/refman/5.6/en/user-variables.html. This code should work with both local and session variables. – Gordon Linoff Jun 23 '15 at 13:49

Vigs · Accepted Answer · 2013-09-16T20:54:05.380

This does the trick:

CREATE PROCEDURE insertToonOneShot(IN locale CHAR(2), IN name VARCHAR(16), IN realm VARCHAR(24), IN faction CHAR(1), IN toon_level INT, IN class_name VARCHAR(12))
BEGIN
SELECT @realmID := id FROM realms WHERE realms.name = realm;
SELECT @classID := id FROM classes WHERE classes.name = class_name;
SELECT @toonID := id FROM toon WHERE toon.name = name AND toon.realm_id = @realmID;
IF NOT @realmID IS NULL AND NOT @classID IS NULL AND @toonID IS NULL
THEN 
INSERT INTO toon (`locale`, `name`, `class_id`, `realm_id`, `faction`, `level`)
VALUES (locale, name, @classID, @realmID, faction, toon_level);
END IF;
END;
//

Apparently the declare statements were not required... Who would have known?

Thanks to Gordon Linoff for pointing me in the right direction.

Take care and understand the difference before using session variables which are prefixed with @, and procedural variables, which are not. The difference between the two is that the procedure variable is reinitialized to NULL each time the procedure is called, while the session-specific variable is not. Imagine a scenario where same session variable is used in Parent procedure. — Himalaya Garg, Jan 19 '18 at 09:42

MYSQL Stored Procedures: Variable Declaration and Conditional Statements

3 Answers3

Linked