CODEWITHSUNDEEP
mysqlsqlstored-procedures
asit_dhal
asit_dhal

Reputation: 1269

dynamic sql in mysql stored proceure

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 ?

Upvotes: 0

Views: 132

Answers (1)

wchiquito
wchiquito

Reputation: 16551

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

Upvotes: 2

Related Questions