Holding the table name in a variable inside a stored procedure using a prepared statement

Question

I am trying to assign the name of a table using a variable which i have defined.My code looks like this

DELIMITER //

CREATE PROCEDURE karawane ()
BEGIN
declare last_inserted_number VARCHAR(100) DEFAULT '0800100200';
declare last_inserted_code VARCHAR(100) DEFAULT 'Lorem Ipsum';
declare current_dataset VARCHAR(100) DEFAULT 'Lorem Ipsum';

set last_inserted_number = (select messagefrom from current_dataset where id = last_insert_id());
set last_inserted_code = (select statuscode from current_dataset where id = last_insert_id());

if(last_inserted_code = 201) then

PREPARE haus FROM 'update ? set the_status = 'online' where device_number = last_inserted_number';
set current_dataset = (select task_name from running_tasks limit 1);
EXECUTE haus USING @current_dataset;

end if;

END //

I hope to use this stored procedure in a trigger like

SET @OLDTMP_SQL_MODE=@@SQL_MODE, SQL_MODE='STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION';
DELIMITER //
CREATE TRIGGER `le_log_trigger` AFTER INSERT ON `messagelog` FOR EACH ROW BEGIN

call karawane();

END//
DELIMITER ;

but my procedure karawane() has this error

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 'online' where device_number = last_inserted_number';
set current_dataset = 

Why is the error occuring?.

Edit my new stored procedure

DELIMITER //

CREATE PROCEDURE karawane ()
BEGIN
declare last_inserted_number VARCHAR(100) DEFAULT '0800100200';
declare last_inserted_code VARCHAR(100) DEFAULT 'Lorem Ipsum';
declare current_dataset VARCHAR(100) DEFAULT 'Lorem Ipsum';

set last_inserted_number = (select messagefrom from current_dataset where id = last_insert_id());
set last_inserted_code = (select statuscode from current_dataset where id = last_insert_id());

if(last_inserted_code = 201) then

SET @cd = (select task_name from running_tasks limit 1);
SET @sql = CONCAT('update ',@cd,' set the_status = ''online'' where device_number = ?';
PREPARE haus FROM @sql;
EXECUTE haus USING last_inserted_number;

end if;

END //

My new error when i try this is

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 '; PREPARE haus FROM @sql; EXECUTE haus USING last_inserted_number;

Answer 1

One issue is a simple syntax problem, single quotes within a string literal must be "escaped" as two single quote characters. This would be syntax error:

SELECT 'It's great'

To get a single quote included in a string literal, you'd do something like this:

SELECT 'It''s great'

---

A bigger issue with your procedure is that you cannot pass an identifier (name of a table, name of column, name of a stored program, etc.) as a parameter. The identifiers MUST be specified in the SQL text. (This is because of how MySQL parses and executes a statement.)

MySQL has to parse for valid syntax (keywords, matching parens, operators, etc.), and for semantics (the identifiers are valid references to tables, columns, stored programs, etc., user has required privileges, etc.

MySQL has to develop an execution plan, including which tables and columns to access, which indexes to use, join order, etc.

Finally, MySQL will execute the prepared statement.

Once you get your brain wrapped around how MySQL processes a query, it becomes clear why identifiers must be part of the actual SQL text, and can't be supplied as arguments.

You can only pass data values as parameters in prepared statements.

---

You can do something like this in your procedure, to change which table you are updating, based on the results of a query:

SET @cd = (select task_name from running_tasks limit 1);
SET @sql = CONCAT('update ',@cd,' set the_status = ''online'' where device_number = ?');
PREPARE haus FROM @sql;
EXECUTE haus USING @last_inserted_number;
DEALLOCATE haus;

You can dynamically create a string that contains SQL text to be executed. You can make use of variables in assigning a value to the string, but when that string is passed to the PREPARE call, all of the identifiers are part of the SQL text.

Note that the SQL statement can contain bind placeholders for values. I've demonstrated that in the WHERE clause of the update statement.

The demonstration uses MySQL user-defined variables (sometimes called "session" variables), as opposed to procedure variables. (User-defined variables start with an @ character; procedure variables are variable names declared within a MySQL stored program, and do not start with an @ character. Within your procedure, you may be able to use procedure variables in place of user-defined variables.