How to store a simple SQL query into user defined variable and execute it in MySQL?

Question

I'm new to MySQL, I'm trying to store sql statement into variable:

SET @sql = 'SELECT * FROM tbl_tableName';
  SELECT @sql;

It executes correctly, and give me result:

But how can see the result of the sql statement stored in that variable? If I'm execute it, it gives an error.

SET @sql = 'SELECT * FROM tbl_defects';
  SELECT @sql;
  PREPARE stmt FROM @sql;
  EXECUTE stmt;

error:

Fatal error: Uncaught Error: Call to a member function getClauses() on null in /opt/lampp/phpmyadmin/vendor/phpmyadmin/sql-parser/src/Utils/Query.php:567 Stack trace: #0

Please any one help me to execute the sql statement stored in variable (Want to execute/check the result stored in that variable. I know we can directly run and check the result in SQL prompt)?

Ganesh Aher · Accepted Answer · 2018-02-13 06:03:15Z

1

To get a result for that variable you have to first create a stored procedure and execute that procedure you will get the answer.

Create stored procedure:

DELIMITER $$
CREATE DEFINER=`root`@`localhost` PROCEDURE `test`()
BEGIN  
SET @sql = ('SELECT * FROM tbl_defects');
  SELECT @sql;
  PREPARE stmt FROM @sql;
  EXECUTE stmt;
  DEALLOCATE PREPARE stmt;
END$$
DELIMITER ;

Hope this help you.

edited Feb 13, 2018 at 6:03

Ganesh Aher

1,1463 gold badges24 silver badges53 bronze badges

answered Feb 13, 2018 at 5:43

Vik Patil

716 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Ganesh Aher Over a year ago

Is there any way to execute it without stored procedure ?

Collectives™ on Stack Overflow

How to store a simple SQL query into user defined variable and execute it in MySQL?

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related