In MySQL: How to Pass a Table Name as Stored Procedure And/Or Function Argument

In MySQL: How to pass a table name as stored procedure and/or function argument?

Prepared statements are what you need.

CREATE  PROCEDURE `test1`(IN tab_name VARCHAR(40) )
BEGIN
 SET @t1 =CONCAT('SELECT * FROM ',tab_name );
 PREPARE stmt3 FROM @t1;
 EXECUTE stmt3;
 DEALLOCATE PREPARE stmt3;
END $$

Mysql stored procedure don't take table name as parameter

An SP cannot be optimized with a dynamic table name, so many DBs, MySQL included, don't allow table names to be specified dynamically.

One way around this is to use Dynamic SQL.

CREATE DEFINER=`root`@`localhost` PROCEDURE `test_proc`(IN serviceName VARCHAR(10),IN newsInfoTable VARCHAR(100))
BEGIN                  
    SET @sql = CONCAT('SELECT COUNT(*) FROM ',newsInfoTable,' WHERE newsServiceName=?;'); 
    PREPARE s1 from @sql;
    SET @paramA = serviceName;
    EXECUTE s1 USING @paramA;
END$$

MySQL Stored Procedure Variable as Table Name concatenated

PREPARE should have no bearing on your ability to successfully set OUT parameters of your procedure

SET DELIMITER //
CREATE PROCEDURE test(IN cid INT, IN ids TEXT, OUT out_int INT)
BEGIN  
  SET @sql = CONCAT('SELECT * FROM `table_', cid, '`', CASE WHEN ids IS NULL THEN '' ELSE CONCAT(' WHERE id IN( ', ids, ')') END);

  PREPARE stmt FROM @sql;
  EXECUTE stmt;
  DEALLOCATE PREPARE stmt;

  SET out_int = 1;
END//
SET DELIMITER ;

Sample usage:

mysql> CALL test(1, '2,3', @out_int);
+------+
| id   |
+------+
|    2 |
|    3 |
+------+
2 rows in set (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

mysql> SELECT @out_int;
+----------+
| @out_int |
+----------+
|        1 |
+----------+
1 row in set (0.00 sec)

Dynamic table name variable in stored procedure

You need to assign @s after you assign the uid variable.

You're also missing the SELECT keyword in your query.

  SET @count_id = 1
  WHILE @count_id > 0 DO
    SET uid = FLOOR(rand() * max + min);
    SET @s = CONCAT('SELECT COUNT(`id`) INTO @count_id FROM `', tablename, '` WHERE `id` = ', uid);
    PREPARE stmt from @s;
    EXECUTE stmt;
    DEALLOCATE PREPARE stmt;
  END WHILE;

But you should actually just prepare the statement once, using a placeholder, which you fill in when using EXECUTE.

  SET @count_id = 1
  SET @s = CONCAT('SELECT COUNT(`id`) INTO @count_id FROM `', tablename, '` WHERE `id` = ?');
  PREPARE stmt from @s;
  WHILE @count_id > 0 DO
    SET @uid = FLOOR(rand() * max + min);
    EXECUTE stmt USING @uid;
  END WHILE;
  DEALLOCATE PREPARE stmt;
  SET uid = @uid;

Note that the parameters to EXECUTE have to be user variables, that's why I changed uid to @uid there. Then we set the output parameter at the end of the loop.

You also need to use a user variable for INTO @count_id.

Can't specify table name on mysql stored procedure

KChason got me started in the right direction, but I had to take it a little further to get the first part working from tips here: https://forums.mysql.com/read.php?98,138495,138908#msg-138908.

DROP PROCEDURE
IF EXISTS `workingversion`;

delimiter |

CREATE PROCEDURE `workingversion` (IN tableName VARCHAR(100))
BEGIN

DECLARE done INT DEFAULT 0 ;
DECLARE a,
 b INT DEFAULT 800000 ;
DROP VIEW IF EXISTS v1; 
SET @stmt_text = CONCAT("CREATE VIEW v1 AS SELECT id FROM ",    tableName,  " ORDER BY id") ; 
PREPARE stmt
FROM
    @stmt_text ; EXECUTE stmt ; DEALLOCATE PREPARE stmt ;
BEGIN

DECLARE cur1 CURSOR FOR SELECT * FROM v1 ;
DECLARE CONTINUE HANDLER FOR SQLSTATE '02000'
SET done = 1 ; OPEN cur1 ;
REPEAT
    FETCH cur1 INTO b ;
IF NOT done THEN
SET @Query = CONCAT('UPDATE ',tableName,' SET `id` = ',a+1,' WHERE `id`=',b);
PREPARE stmt FROM @Query;
EXECUTE stmt;

        SET a = a+1;
END
IF ; UNTIL done
END
REPEAT
    ; CLOSE cur1 ;
END ;
END

use a variable for table name in mysql sproc

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.

Variable Table Name MySQL Stored Function

After some research, apparently a stored function will not work in this case due to the fact stored functions cannot execute dynamic SQL. I change my implementation to a stored procedure.

delimiter $$

create procedure parent(tableName varchar(15), nodeId int)
begin
    set @s := concat('select parent from ', tableName, ' where id =', nodeId);
    prepare query from @s;
    execute query;
    deallocate prepare query;
end$$

delimiter ;

how to pass table name into a function?

declare varchar(max) @mySQL
set @mySQL = 'DELETE FROM ' + @tablename + 'WHERE id = ' + Convert(varchar, @the_id)
sp_executeSQL @mySQL

and to make this work on MySQL (as the commenters pointed out) it should look like this:

mysql> prepare stmt from -> 'DELETE FROM ' + @tablename + 'WHERE id = ' + Convert(varchar, @the_id)
mysql> execute stmt; 

Holding the table name in a variable inside a stored procedure using a prepared statement

One issue is a simple syntax problem, single quotes within a string literal must be "escaped" as two single quote characters. This would be syntax error:

SELECT 'It's great'

To get a single quote included in a string literal, you'd do something like this:

SELECT 'It''s great'

---

A bigger issue with your procedure is that you cannot pass an identifier (name of a table, name of column, name of a stored program, etc.) as a parameter. The identifiers MUST be specified in the SQL text. (This is because of how MySQL parses and executes a statement.)

MySQL has to parse for valid syntax (keywords, matching parens, operators, etc.), and for semantics (the identifiers are valid references to tables, columns, stored programs, etc., user has required privileges, etc.

MySQL has to develop an execution plan, including which tables and columns to access, which indexes to use, join order, etc.

Finally, MySQL will execute the prepared statement.

Once you get your brain wrapped around how MySQL processes a query, it becomes clear why identifiers must be part of the actual SQL text, and can't be supplied as arguments.

You can only pass data values as parameters in prepared statements.

---

You can do something like this in your procedure, to change which table you are updating, based on the results of a query:

SET @cd = (select task_name from running_tasks limit 1);
SET @sql = CONCAT('update ',@cd,' set the_status = ''online'' where device_number = ?');
PREPARE haus FROM @sql;
EXECUTE haus USING @last_inserted_number;
DEALLOCATE haus;

You can dynamically create a string that contains SQL text to be executed. You can make use of variables in assigning a value to the string, but when that string is passed to the PREPARE call, all of the identifiers are part of the SQL text.

Note that the SQL statement can contain bind placeholders for values. I've demonstrated that in the WHERE clause of the update statement.

The demonstration uses MySQL user-defined variables (sometimes called "session" variables), as opposed to procedure variables. (User-defined variables start with an @ character; procedure variables are variable names declared within a MySQL stored program, and do not start with an @ character. Within your procedure, you may be able to use procedure variables in place of user-defined variables.

How to take table name as an input parameter to the stored procedure?

CREATE PROCEDURE xyz 
@TableName NVARCHAR(128) 
AS 
BEGIN 
  SET NOCOUNT ON;
  DECLARE @Sql NVARCHAR(MAX);

SET @Sql = N'SELECT TOP 10 * INTO #Temp_Table_One 
              FROM ' + QUOTENAME(@TableName)
          + N' SELECT * FROM #Temp_Table_One '

 EXECUTE sp_executesql @Sql

END

---

Related Topics