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

Published on 26-Feb-2019 11:34:29

Previous Page Print Page