MySQL DECLARE/SELECT INTO Syntax error?

Question

I honestly have no idea what I'm doing wrong here.

Routine Name: procInsertName
Type: PROCEDURE
Parameters: IN Name VARCHAR 30 Charset

DECLARE tempNameID INT;

SELECT nameID
INTO tempNameID
FROM name
WHERE value = @Name;

IF tempNameID IS NULL THEN

    INSERT INTO name (value)
    VALUES (@Name);

END IF;

Is deterministic: unchecked
Definer: <blank>
Security type: DEFINER
SQL data access: CONTAINS SQL
Comment: <blank>

I'm coming out with an error of:

One or more errors have occurred while processing your request:

The following query has failed: "CREATE PROCEDURE `procInsertName`(IN `Name` VARCHAR(30)) NOT DETERMINISTIC CONTAINS SQL SQL SECURITY DEFINER DECLARE   tempNameID INT;

SELECT  nameID
INTO    tempNameID
FROM    name
WHERE   value = @Name;

IF tempNameID IS NULL THEN

    INSERT INTO name (value)
    VALUES (@Name);

END IF;"

MySQL said: #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 'DECLARE  tempNameID INT;

SELECT  nameID
INTO    tempNameID
FROM    name
WHERE   valu' at line 1

Any help would be appreciated, I've spent the last 30 minutes looking around online and can't find anything. Thanks.

You didn't initialize DECLARE to a value, that may be why. For example, DECLARE tempNameID INT unsigned DEFAULT 1; — ryekayo
– ryekayo, Commented Oct 10, 2014 at 15:36

Ruslan · Accepted Answer · 2014-10-10 16:24:04Z

1

I think the error might by lying in the @ symbol... You must be coming from an SQL Server background; MySQL doesn't use the @ notation for variables so your parameter is effectively just called Name. Try to rename your Name parameter to p_Name and then replace the 2 instances of @Name with p_Name in the body of your stored proc.

I.e. like this:

CREATE PROCEDURE `procInsertName`(IN `p_Name` VARCHAR(30)) NOT DETERMINISTIC CONTAINS SQL SQL SECURITY DEFINER 
DECLARE   tempNameID INT;

SELECT  nameID
INTO    tempNameID
FROM    name
WHERE   value = p_Name;

IF tempNameID IS NULL THEN

    INSERT INTO name (value)
    VALUES (p_Name);

END IF;

answered Oct 10, 2014 at 16:24

Ruslan

2,8092 gold badges22 silver badges32 bronze badges

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

2 Comments

XstreamINsanity Over a year ago

I've seen examples go both ways. Maybe it was dependent on the version. However, I changed up my procedure and this actually helped it as well. Thanks.

Ruslan Over a year ago

I think @Name is a legal identifier in MySQL (as I am also pretty sure I've seen it used, but don't know if its legal in all versions) but then you'd have to declare your parameter as @Name in the stored proc's signature as well (i.e. IN @Name VARCHAR(30)). That being said, I know that by convention the @ symbol is used for something else in MySQL (possibly local variables, but my memory fails me).

Collectives™ on Stack Overflow

MySQL DECLARE/SELECT INTO Syntax error?

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related