CODEWITHSUNDEEP
mysqlprepared-statement
kdragger
kdragger

Reputation: 446

mysql prepare: session variables vs parameters & local variables

I'm using a stored procedure and prepare statements. I'd like to send in parameters to define which column to return from a select statement. This works if I use session variables and don't substitute variables. I can work around this, but I'd like to understand the issue. Here are examples:

This works:

DELIMITER $$
DROP PROCEDURE IF EXISTS `pnlByTheme` $$
CREATE PROCEDURE `pnlByTheme`(IN param VARCHAR(50))
BEGIN
  set @sqlStmt = concat("select ",param," from pnl_aggregate");
  prepare stmt from @sqlStmt;
  execute stmt;
deallocate prepare stmt;
END $$
DELIMITER ;

When I call pnlByTheme('label'), I get the results of select label from pnl_aggregate, as I would from the command line. If I change the set statement to:

set @sqlStmt = "select ? from pnl_aggregate";

and then I change the execute statement to:

execute stmt using param;

mysql does not like that and won't create the procedure. if i instead change to:

DELIMITER $$

DROP PROCEDURE IF EXISTS `pnlByTheme` $$
CREATE PROCEDURE `pnlByTheme`(IN param VARCHAR(50))
BEGIN
  set @p = param;
  set @sqlStmt = "select ? from pnl_aggregate";
  prepare stmt from @sqlStmt;
  execute stmt using @p;
deallocate prepare stmt;
END $$

DELIMITER ;

that will create the procedure. why? what's strange (to me, anyway) is that the results of the call 

call pnlbytheme('label');

are just literally "label" for each row rather than the actual data that I am looking for. I can easily work around this using concat, but want to understand. please help.

Upvotes: 3

Views: 3102

Answers (1)

kdragger
kdragger

Reputation: 446

Okay, well thanks for those that looked. I found the answers when related questions showed up after posting. Sometimes we (or at least I) don't always know how to search for what we want. At any rate, here are the two answers:

1) Prepared statements are session global. See Dynamic MySQL with local variables and http://dev.mysql.com/doc/refman/5.1/en/sql-syntax-prepared-statements.html (look for "A prepared statement is also global to the session."). Although I am not 100% clear on this, the result is that requires session variables. The answer was by Justin Grant.

2) "Parameter markers can be used only where data values should appear, not for SQL keywords, identifiers, and so forth." from http://dev.mysql.com/doc/refman/5.7/en/prepare.html, which was answered here mysql stored procedure oddity by Devart.

I also found MySQL: @variable vs. variable. Whats the difference? helpful. Thanks, Quassnoi.

Upvotes: 3

Related Questions