CODEWITHSUNDEEP
mysqlstored-proceduresprepared-statement
Alain
Alain

Reputation: 1470

In mysql dynamic stored proc, is @var required to build a prepared statement?

I have a similar stored proc (but longer). It is called from PHP (GET request on Apache)

delimiter // 
CREATE PROCEDURE dynamic(IN tbl CHAR(64), IN col CHAR(64))
BEGIN
    SET @full_statement = CONCAT('SELECT ',col,' FROM ',tbl );
    PREPARE stmt FROM @full_statement;
    EXECUTE stmt;
END
//
delimiter ;

From what I read, @s is a mysql session variable living as long as my session is alive. The @s presence annoys me since I fear that 2 concurrent request on that stored proc might play with this "global variable". So I tried to remove the '@' like this

delimiter // 
CREATE PROCEDURE dynamic(IN tbl CHAR(64), IN col CHAR(64))
BEGIN
    DECLARE full_statement VARCHAR(300);
    SET full_statement = CONCAT('SELECT ',col,' FROM ',tbl );
    PREPARE stmt FROM full_statement;
    EXECUTE stmt;
END
//
delimiter ;

to build the prepared statement without success.

I seem to have constantly

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 'full_statement; EXECUTE stmt;

Regards.

Upvotes: 0

Views: 149

Answers (1)

Bill Karwin
Bill Karwin

Reputation: 562671

Yes, the @var is required.

MySQL's PREPARE statement only accepts "user variables" (those prefixed with @), not local variables declared in a stored routine.

This has long been recognized as a WTF in MySQL:

Does my fear of 2 calls within my php session is really a problem ?

No. It's not a global variable, it's a session variable.
Two concurrent sessions have their own value for @var.

Upvotes: 1

Related Questions