MYSQL Stored Procedures: Variable Declaration and Conditional Statements

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 ;

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;

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))BEGINSELECT @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 NULLTHEN 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.