CODEWITHSUNDEEP
mysqlsqlstored-proceduresvariable-declaration
Marven88
Marven88

Reputation: 31

Declare variable in stored procedure

I have problem with declare variable in stored procedure. I used MySQL. My example code:

CREATE PROCEDURE `name`()
BEGIN
    DECLARE varname INT;

    SELECT  * FROM `table` INTO var;
END

MySQL returns error:

error 1064 - You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '' at line 3

Upvotes: 0

Views: 12324

Answers (2)

fancyPants
fancyPants

Reputation: 51948

When you have multiple statements in a procedure you have to change the delimiter. Otherwise MySQL thinks that the procedure declaration is finished after the first statement.

Here's an example that works. And btw, var is not a reserved keyword like others are trying to tell you.

DELIMITER $$
CREATE PROCEDURE test ()
BEGIN
    DECLARE var INT;
    SELECT 1 INTO var;
    SELECT var;
END $$
DELIMITER ;

CALL test();
+------+
| var  |
+------+
|    1 |
+------+
1 row in set (0.00 sec)

Upvotes: 4

Tushar Gupta
Tushar Gupta

Reputation: 15943

var is a reserved key word

CREATE PROCEDURE test ()
BEGIN
    DECLARE  var2 INT;
    // Do something
END;

Update :

I saw MAriaDB in the error , the correct way to declare variable in MariaDB is

SET @var = 0;

You should tag your question with the correct keywords

Upvotes: 2

Related Questions