mysql 存储过程 using,MySQL：将过程参数传递给EXECUTE USING语句

This is MySQL 5.1.

(Note: I realize there are better ways of doing this particular example, this is not my real code)

Here is what I want to do:

The below procedure gets created, but when I CALL it, I get "ERROR 1210 (HY000): Incorrect arguments to EXECUTE"

DELIMITER //

CREATE PROCEDURE get_users_by_state(IN state CHAR(2))

READS SQL DATA

BEGIN

SET @mystate = state;

SET @sql = CONCAT('SELECT * FROM test_table WHERE state = "?"');

PREPARE stmt FROM @sql;

EXECUTE stmt USING @mystate;

END;

//

CALL get_users_by_state('AA')//

ERROR 1210 (HY000): Incorrect arguments to EXECUTE

Is there a way to pass the procedure's parameters to the EXECUTE USING statement?

Here is a version that does indeed work, but irks me:

CREATE PROCEDURE get_users_by_state(IN state CHAR(2))

READS SQL DATA

BEGIN

SET @sql = CONCAT('SELECT * FROM test_table WHERE state = "', state, '"')

PREPARE stmt FROM @sql;

EXECUTE stmt;

END;

//

As a side-question, does MySQL have any facilities for escaping strings, like Postgres' quote_literal() and quote_ident()?

For a point of reference, here's something somewhat equivalent for Postgres:

CREATE OR REPLACE FUNCTION get_info_by_state(character)

RETURNS SETOF ret_type AS

$BODY$

DECLARE

sql text;

BEGIN

sql := 'SELECT uid, some_data FROM test_table WHERE state = ' || quote_literal($1);

RETURN QUERY EXECUTE sql;

END

$BODY$

LANGUAGE 'plpgsql' VOLATILE

Thanks!

解决方案

I don't think you need double quotes around the parameter holder.

Update Here, lest there be no misunderstanding:

DELIMITER //

CREATE PROCEDURE get_users_by_state(IN state CHAR(2))

READS SQL DATA

BEGIN

SET @mystate = state;

SET @sql = CONCAT('SELECT * FROM test_table WHERE state = ?');

PREPARE stmt FROM @sql;

EXECUTE stmt USING @mystate;

END;

//