Mysql stored procedure don’t take table name as parameter

by

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

More Related Contents:

  1. Stored procedure that Automatically delete rows older than 7 days in MYSQL
  2. Pass array to MySQL stored routine
  3. Connect By Prior Equivalent for MySQL
  4. How to raise an error within a MySQL function
  5. MySQL stored procedure vs function, which would I use when?
  6. MySQL stored procedures use them or not to use them
  7. Looping Over Result Sets in MySQL
  8. MySQL create stored procedure syntax with delimiter
  9. mysql dynamic query in stored procedure
  10. MySQL : transaction within a stored procedure
  11. Writing optional parameters within stored procedures in MySQL? [duplicate]
  12. Dynamic table names in stored procedure function
  13. List of Stored Procedures/Functions Mysql Command Line
  14. mysql stored procedure that calls itself recursively
  15. MySQL triggers cannot update rows in same table the trigger is assigned to. Suggested workaround?
  16. How do you debug MySQL stored procedures?
  17. How can I use transactions in my MySQL stored procedure?
  18. MySQL Stored Procedure Error Handling
  19. MySQL error #2014 – Commands out of sync; you can’t run this command now
  20. use a variable for table name in mysql sproc
  21. Split a string and loop through values in MySQL stored procedure
  22. How to schedule a stored procedure in MySQL
  23. My SQL Dynamic query execute and get ouput into a variable in stored procedure
  24. MySQL : When stored procedure parameter name is the same as table column name
  25. What is dynamic SQL?
  26. How to use Table output from stored MYSQL Procedure
  27. How do I write an SP in phpMyAdmin (MySQL)?
  28. Creating MYSQL Procedure in Laravel 4 Migrations
  29. is it possible to call a sql script from a stored procedure in another sql script?
  30. How to pass a variable to a IN clause?

Leave a Comment