MySql Stored Procedure Select Table

Question

Where I have more than one table in my database for use with (similar, but) different products.

Is it possible to select a table to work with based on a parameter (example below)? (This would save me having multiple similar copies of the same procedure).

Cheers.

---

DELIMITER $$

DROP PROCEDURE IF EXISTS `dostuff` $$
CREATE PROCEDURE `dostuff`(IN prod_code VARCHAR(10))
BEGIN

IF INSTR(prod_code, 'product_a') THEN
    myTable = product_a_table
ELSE IF INSTR(prod_code, 'product_b') THEN
    myTable = product_b_table
END IF

-- do stuff on myTable such as SELECT and UPDATE

END $$

DELIMITER ;

Answer 1

I believe it's not possible to have variable table names in a query.

Our very own Bill Karwin explains here

Table names, column names, etc. cannot be dynamic in the way you describe. This is not permitted by the SQL language, for many reasons.

For instance, there would be no way for the query optimizer to decide which index(es) to use, if it doesn't know at parse time which tables and columns are being queried.

Answer 2

It is possible to create a command like this:

SET @query := CONCAT("REPAIR TABLE ", tableName);             
PREPARE stmt FROM @query;             
EXECUTE stmt;             
DEALLOCATE PREPARE stmt;

As you can see, you can push any string/variable you want into that command (using variables wherever you'd like.

Bobby

Answer 3

I think really the issue is whether your database has the required level of normalisation. Why is "ProductGroup" not a field on a sinlge product table, thus allowing you to select rows where the ProductGroup has a certain value?