MySQL “not a variable or NEW pseudo-variable” message. What is this error in my Stored Procedure?

To get rid of this error message, let us see a sample example. But before that let us go through the concept to fix it.

Use variable to get the value from stored procedure. The variable will prefix with @ symbol. The syntax is as follows −

CALL yourStoredProcedureName(yourParameter1,yourParameter2,..........N,@yourVariableName);

To see the value of variable you need to use select statement. The syntax is as follows −

SELECT @yourVariableName;

To understand the above syntax, let us create a table and insert some records in the table.

The query to create a table is as follows −

mysql> create table StoredProcedureDemo
   -> (
   -> Id int,
   -> Name varchar(10)
   -> );
Query OK, 0 rows affected (1.20 sec)

Now insert some records in the table using insert command. The query is as follows −

mysql> insert into StoredProcedureDemo values(1,'John');
Query OK, 1 row affected (0.31 sec)
mysql> insert into StoredProcedureDemo values(2,'Carol');
Query OK, 1 row affected (0.19 sec)

Display all records from the table using select statement. The query is as follows −

mysql> select *from StoredProcedureDemo;

The following is the output −

+------+-------+
| Id   | Name  |
+------+-------+
|    1 | John  |
|    2 | Carol |
+------+-------+
2 rows in set (0.00 sec)

Here is the stored procedure that returns a value. The query is as follows to create a stored procedure −

mysql> DELIMITER //
mysql> create procedure Update_table(IN Id int, IN Name VARCHAR(20),OUT message VARCHAR(50))
   -> BEGIN
   -> set message= 'DATABASE HAS BEEN UPDATED';
   -> INSERT INTO StoredProcedureDemo
   -> values (Id, Name);
   -> end;
   -> //
Query OK, 0 rows affected (0.67 sec)
mysql> DELIMITER ;

Now call the stored procedure with some values −

mysql> call Update_table(3,'Larry',@UserMessage);

Query OK, 1 row affected (0.13 sec)

Check the value of variable @UserMessage using select statement. The query is as follows −

mysql> select @UserMessage;

The following is the output −

+---------------------------+
| @UserMessage              |
+---------------------------+
| DATABASE HAS BEEN UPDATED |
+---------------------------+
1 row in set (0.00 sec)

Now check the table records once again. The query is as follows −

mysql> select *from StoredProcedureDemo;

The following is the output −

+------+-------+
| Id   | Name  |
+------+-------+
|    1 | John  |
|    2 | Carol |
|    3 | Larry |
+------+-------+
3 rows in set (0.00 sec)

Look at the above sample output, a new record with id 3 has been inserted. Now you won’t get the error message.

Samual Sam

Updated on 30-Jul-2019 22:30:24

• Related Questions & Answers
• Check for NULL or empty variable in a MySQL stored procedure
• Stored procedure using variable in LIMIT expression?
• What is STORED PROCEDURE in a DB2? How will you create a new stored procedure?
• Store a column's value into a MySQL stored procedure’s variable
• What is use of $error variable in PowerShell?
• How to display message from a stored procedure?
• How to declare a variable inside a procedure in MySQL?
• MySQL Stored Procedure DEFINER=`root`@`%` is not working in localhost?
• How can we write MySQL handler, in a stored procedure, that throws an error message and continues the execution?
• How can we write MySQL handler, in a stored procedure, that throws an error message and exit the execution?
• In MySQL stored procedures, how to check if a local variable is null?
• How do I avoid the variable value in a MySQL stored procedure to change when records are updated?
• How can a MySQL stored procedure call another MySQL stored procedure inside it?
• What is blank or uninitialized final variable in Java?
• What is stored procedure and how can we create MySQL stored procedures?