CODEWITHSUNDEEP
mysqlstored-proceduresquery-string
Kyle
Kyle

Reputation: 11119

use a variable for table name in mysql sproc

I'm trying to pass a table name into my mysql stored procedure to use this sproc to select off of different tables but it's not working...

this is what I"m trying:

CREATE PROCEDURE `usp_SelectFromTables`(
 IN TableName varchar(100)
)
BEGIN
        SELECT * FROM @TableName;
END

I've also tried it w/o the @ sign and that just tells me that TableName doesn't exist...which I know :)

Upvotes: 44

Views: 90931

Answers (3)

codewaggle
codewaggle

Reputation: 4943

An extra bit that caused me problems.

I wanted to set the table name and field dynamically in a query as @kyle asked, but I also wanted to store the result of that query into a variable @a within the query.

Instead of putting the variable @a into the concat literally, you need to include it as part of the string text.

delimiter //

CREATE PROCEDURE removeProcessed(table_name VARCHAR(255), keyField VARCHAR(255), maxId INT, num_rows INT)

BEGIN
  SET @table_name = table_name;
  SET @keyField = keyField;
  SET @maxId = maxId;
  SET @num_rows = num_rows;

  SET @sql_text1 = concat('SELECT MIN(',@keyField,') INTO @a FROM ',@table_name);
  PREPARE stmt1 FROM @sql_text1;
  EXECUTE stmt1;
  DEALLOCATE PREPARE stmt1;

  loop_label:  LOOP
    SET @sql_text2 = concat('SELECT ',@keyField,' INTO @z FROM ',@table_name,' WHERE ',@keyField,' >= ',@a,' ORDER BY ',@keyField,' LIMIT ',@num_rows,',1');
    PREPARE stmt2 FROM @sql_text2;
    EXECUTE stmt2;
    DEALLOCATE PREPARE stmt2;

    ...Additional looping code...

    END LOOP;
END
//

delimiter ;

So in @sql_text1 assign the result of the query to @a within the string using:

') INTO @a FROM '

Then in @sql_text2 use @a as an actual variable:

,' WHERE ',@keyField,' >= ',@a,' ORDER BY '

Upvotes: 19

Angelo
Angelo

Reputation: 727

SET @cname:='jello';
SET @vname:='dwb';
SET @sql_text = concat('select concept_id,concept_name,',@vname,' from enc2.concept a JOIN enc2.ratings b USING(concept_id) where concept_name like (''%',@cname,'%'') and 3 is not null order by 3 asc');

PREPARE stmt FROM @sql_text;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

Upvotes: 71

Jonathan Leffler
Jonathan Leffler

Reputation: 753585

It depends on the DBMS, but the notation usually requires Dynamic SQL, and runs into the problem that the return values from the function depend on the inputs when it is executed. This gives the system conniptions. As a general rule (and therefore probably subject to exceptions), DBMS do not allow you to use placeholders (parameters) for structural elements of a query such as table names or column names; they only allow you to specify values such as column values.

Some DBMS do have stored procedure support that will allow you to build up an SQL string and then work with that, using 'prepare' or 'execute immediate' or similar operations. Note, however, that you are suddenly vulnerable to SQL injection attacks - someone who can execute your procedure is then able to control, in part, what SQL gets executed.

Upvotes: 17

Related Questions