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?...

Answer 1

Old question, but I think it's worth mentioning that it seems to be confusing session variables, which are prefixed with @, with procedural variables, which are not.

The accepted solution resolves the error, but could introduce problems related to variable scope, if a variable is defined outside the procedure and then used inside. The correct way to resolve this is to use only procedural variables:

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 ;

Answer 2

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;

Answer 3

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.