CODEWITHSUNDEEP
mysqlsqlstored-proceduresvariable-declaration
Vigs
Vigs

Reputation: 1331

MYSQL Stored Procedures: Variable Declaration and Conditional Statements

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

Upvotes: 18

Views: 112268

Answers (3)

miken32
miken32

Reputation: 42711

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 ;

Upvotes: 19

Vigs
Vigs

Reputation: 1331

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.

Upvotes: 11

Gordon Linoff
Gordon Linoff

Reputation: 1270431

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;

Upvotes: 13

Related Questions