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.
Published on 26-Feb-2019 16:04:29
