Mikhail Mikhail - 4 months ago 9
MySQL Question

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

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.

Answer

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$$
Comments