CODEWITHSUNDEEP
mysql
XstreamINsanity
XstreamINsanity

Reputation: 4296

MySQL DECLARE/SELECT INTO Syntax error?

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.

Upvotes: 0

Views: 1230

Answers (1)

Ruslan
Ruslan

Reputation: 2799

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;

Upvotes: 1

Related Questions