A MySQL delete-from-table-by-id stored procedure with a table name and an ID number as parameters

Question

I need to write a MySQL delete-from-table-by-id stored procedure with a table name and an ID number as parameters sent to the procedure when invoked. The table name parameter tells which table to delete from and the ID parameter tells which row in the table to be deleted.

Here's my non-working solution:

CREATE PROCEDURE delete_from_table_by_id(IN IN_TABLE_NAME VARCHAR(255),
                                         IN IN_ROW_ID     INT UNSIGNED)
BEGIN
    DELETE FROM IN_TABLE_NAME
    WHERE CONCAT(IN_TABLE_NAME, '_id') = IN_ROW_ID;
END$$

It should be working like this: I have tables named book, author, publisher, et cetera. The ID column in each table in my database is made up of two parts, namely, table_name and _id. So, in the author table the ID column is named author_id, in the book table it's named book_id. For example, the following code should get rid of the row with ID 456 from the book table:

CALL delete_from_table_by_id('book', 456);

Thank you all in advance.

possible duplicate of [Mysql store procedure don't take table name as parameter](http://stackoverflow.com/questions/6609778/mysql-store-procedure-dont-take-table-name-as-parameter) — hjpotter92, Apr 17 '13 at 20:32

score 1 · Answer 1 · answered Apr 17 '13 at 20:53

You can not use a variable for a table name or for column name.

The simplest solution for you is to use prepare and execute.

SET @s = 'DELETE FROM ? WHERE CONCAT(?, '_id') = ?';

PREPARE stmt2 FROM @s;

SET @inTableName = 'some_table';
SET @inRowId = 42;

EXECUTE stmt2 USING @inTableName, @inTableName, @inRowId;

I pulled this code out of my head; I haven't tried it out. But I'm pretty sure it should work.

score 0 · Accepted Answer · answered Apr 17 '13 at 21:29

Thanks. Here's my code that works:

CREATE PROCEDURE delete_from_table_by_id(IN IN_TABLE_NAME VARCHAR(255),
                                         IN IN_ROW_ID     INT UNSIGNED)
BEGIN
    SET @SQL = CONCAT('DELETE FROM ', IN_TABLE_NAME,
                      ' WHERE ', IN_TABLE_NAME,
                      '_id = ', IN_ROW_ID);
    PREPARE stmt FROM @SQL;
    EXECUTE stmt;
    DEALLOCATE PREPARE stmt;
END$$

A MySQL delete-from-table-by-id stored procedure with a table name and an ID number as parameters

2 Answers2