CODEWITHSUNDEEP
mysqlstored-procedures
kapso
kapso

Reputation: 11903

Mysql (5.1.41) dynamic SQL in stored procedure issue

I have a stored procedure that populates a temp table. The temp table is populated using multiple dynamic SQL (it has "having" & "between" clause). I am executing this SQL inside my stored procedure using:

set @sql = concat("insert into my_temp select * from my_table where my_date between ", date1, " and ", date2)
PREPARE stmt1 FROM @sql;
EXECUTE stmt1;
DEALLOCATE PREPARE stmt1;

/* more prepared dynamic statements */

The problem is my driver complaints as soon as it encounters the first EXECUTE statement, apparently it thinks MySQL is trying to return a resultset from a stored procedure. Is that how mysql behaves when it comes to dynamic sqls in stored procedures?

I get this error from ruby/rails/mysql2 driver - 

Mysql2::Error: PROCEDURE my_db.sp_special_customers can't return a result set in the given context:

Basically the driver does not support returning result-sets from stored procedure, which is fine. And that's not the issue, the issue for me is why does my driver think that EXECUTE stmt1 means a result set is being returned?

Is there a way in Mysql to fix this?

Upvotes: 1

Views: 672

Answers (1)

Derek Downey
Derek Downey

Reputation: 1532

Have a look at: http://dev.mysql.com/doc/refman/5.0/en/stored-program-restrictions.html

Particularly: "Stored routines cannot contain arbitrary SQL statements. The following statements are not permitted: "

They don't allow 'EXECUTE' in the stored procedure. You can however use prepared statements if you're using mysql 5.x. It might be a viable alternative, depending on what you're trying to accomplish

Upvotes: 0

Related Questions