dynamic sql in mysql stored proceure

Question

I am trying to write a generic sql procedure that can execute any string as a sql statement.

Here is the procedure definition.

DELIMITER //

DROP PROCEDURE IF EXISTS execute_dynamic_sql;
CREATE PROCEDURE execute_dynamic_sql (IN sql_query longtext)
BEGIN
SELECT sql_query;
PREPARE stmt FROM @sql_query;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
END //

I am calling the above function like this

mysql> call execute_dynamic_sql('show tables');
    -> //
+-------------+
| sql_query   |
+-------------+
| show tables |
+-------------+
1 row in set (0.00 sec)

ERROR 1064 (42000): 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
mysql> #

Can somebody tell me why is the error coming ?

... Why are you writing a procedure to execute a query? Why not just execute the query? — ceejayoz
– ceejayoz, Commented Jan 26, 2015 at 13:06
I have to make a lot of sql statements and execute it. So, I moved the 4 common lines to another procedure. — asit_dhal
– asit_dhal, Commented Jan 26, 2015 at 13:09

wchiquito · Accepted Answer · 2015-01-26 13:38:41Z

2

It is important to indicate the difference between 9.4. User-Defined Variables and routine parameters 13.1.15. CREATE PROCEDURE and CREATE FUNCTION Syntax, are different variables.

In your example @sql_query is NULL and sql_query is assigned to SHOW TABLES.

Try:

DELIMITER//

CREATE PROCEDURE `execute_dynamic_sql` (IN `sql_query` LONGTEXT)
BEGIN
  SET @`sql_query` := `sql_query`;
  PREPARE `stmt` FROM @`sql_query`;
  EXECUTE `stmt`;
  DEALLOCATE PREPARE `stmt`;
END//

DELIMITER;

SQL Fiddle demo

edited Jan 26, 2015 at 13:38

answered Jan 26, 2015 at 13:33

wchiquito

16.6k2 gold badges37 silver badges46 bronze badges

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

Collectives™ on Stack Overflow

dynamic sql in mysql stored proceure

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related