Dynamic my sql error

I am below error when i try to execute procedure.

delimiter $$
USE `Test`$$
DROP PROCEDURE IF EXISTS `DYNAMIC_MYSQL`$$
CREATE PROCEDURE DYNAMIC_MYSQL()
BEGIN
DECLARE DONE INT;
DECLARE CHECKNUMBER VARCHAR(50);
DECLARE QUERY1 VARCHAR(100);
DECLARE stmt VARCHAR(100);
SET NUMBER='3131';

SET QUERY1=CONCAT('SELECT number FROM abc WHERE number LIKE "%',NUMBER,'%"');
PREPARE stmt FROM @QUERY1;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
SELECT QUERY1;

END;

Error:CALL Test.DYNAMIC_MYSQL() Error Code: 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 ‘NULL’ at line 1 0.000 sec

Please share your suggestions on this.

You’re using two different kinds of variables.

QUERY1 is a stored program variable, with program scope, while @QUERY1 is a user-defined variable, with session scope.

Prepared statements also have session scope, so they only work with user-defined variables… but your procedure never initializes @QUERY1, so it’s NULL. Hence, the error message.

You can remove the DECLARE QUERY1 statement and change the SET statement to SET @QUERY1 = .. and your code should work.

However, it’s not a good practice to concatenate strings together. You need to use placeholders.

SET @NUMBER='3131'; 
SET @QUERY1='SELECT number FROM abc WHERE number LIKE CONCAT("%",?,"%")';
PREPARE stmt FROM @QUERY1; EXECUTE stmt USING @NUMBER;

Here, the value in the variable @NUMBER will be used in place of the ?, but not by simple string manipulation. It’s impossible, when building a query this way for the server to be manipulated into getting the SQL and the data confused, and opening up a SQL injection vulnerability.

As originally written, if NUMBER was ever changed to use a string that was passed in to the procedure from outside, you would have code that’s potentially easily manipulated into doing something you don’t want.

Also, never declare program variables with the same names as columns. That creates an ambiguous situation, in which WHERE number... could be interpreted as referring to the variable, not the column, which is also not what you want. MySQL does not warn you when you do this, but unexpected results are guaranteed.