variable table/field names in stored procedures

[zeno]

Hi All !

is it possible to use variable table and/or field names in stored procedures i.e. construct the name of a table/field based on an input parameter as in :

create procedure toto(in p_name char(5), out p_count int)
begin
select count(*) into p_count from table_'p_name'
end
 

where the table name from which I am selecting is the concatenation of the prefix 'table_' and the value of the input parameter p_name.

Cheers,

M.

[zeno]

Hi All !

I answered my own question. To do so, construct your SQL statement dynamically with the CONCAT() function and then PREPARE and EXECUTE your query with it.

HTH,

Martin.

[rebeccam]

Hello! I am having a similar problem, but with the name of the database. Could you please share more information about how to use the CONCAT() function and then PREPARE and EXECUTE? Thanks!

rebeccam

Hi All !

I answered my own question. To do so, construct your SQL statement dynamically with the CONCAT() function and then PREPARE and EXECUTE your query with it.

HTH,

Martin.

[gwk]

HI here's how it works

CREATE TABLE `tab` (
`mycol` INTEGER UNSIGNED NOT NULL DEFAULT NULL AUTO_INCREMENT,
PRIMARY KEY (`mycol`)
)ENGINE = InnoDB;

SET @A = "mycol";
SET @B = CONCAT("SELECT ",@a," FROM tab;");
PREPARE stmt1 FROM @b;
EXECUTE stmt1;
DEALLOCATE PREPARE stmt1;