Dynamic table names in stored procedure function

by

If you want to buld a SQL statement using identifiers, then you need to use prepared statements; but prepared statements cannot be used in functions. So, you can create a stored procedure with OUT parameter –

CREATE PROCEDURE getName
 (IN tableName VARCHAR(50), IN myId INT(11), OUT myName VARCHAR(50))
BEGIN

  SET @GetName =
    CONCAT('SELECT name INTO @var1 FROM ', tableName, ' WHERE id=', myId);
  PREPARE stmt FROM @GetName;
  EXECUTE stmt;

  SET myName = @var1;
END

Using example –

SET @tableName="tbl";
SET @myId = 1005;
SET @name = NULL;
CALL getName(@tableName, @myId, @name);
SELECT @name;

More Related Contents:

  1. Stored procedure that Automatically delete rows older than 7 days in MYSQL
  2. Can I create view with parameter in MySQL?
  3. Pass array to MySQL stored routine
  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. Illegal mix of collations (utf8_unicode_ci,IMPLICIT) and (utf8_general_ci,IMPLICIT) for operation ‘=’
  8. MySQL create stored procedure syntax with delimiter
  9. mysql dynamic query in stored procedure
  10. Writing optional parameters within stored procedures in MySQL? [duplicate]
  11. List of Stored Procedures/Functions Mysql Command Line
  12. passing LIMIT as parameters to MySQL sproc
  13. mysql stored procedure that calls itself recursively
  14. How do you debug MySQL stored procedures?
  15. How can I use transactions in my MySQL stored procedure?
  16. MySQL Stored Procedure Error Handling
  17. MySQL Stored Procedures not working with SELECT (basic question)
  18. use a variable for table name in mysql sproc
  19. Split a string and loop through values in MySQL stored procedure
  20. How to schedule a stored procedure in MySQL
  21. My SQL Dynamic query execute and get ouput into a variable in stored procedure
  22. MySQL : When stored procedure parameter name is the same as table column name
  23. What is dynamic SQL?
  24. How to use Table output from stored MYSQL Procedure
  25. How do I write an SP in phpMyAdmin (MySQL)?
  26. Call a stored procedure for each row returned by a query in MySQL
  27. Creating MYSQL Procedure in Laravel 4 Migrations
  28. is it possible to call a sql script from a stored procedure in another sql script?
  29. Mysql stored procedure don’t take table name as parameter
  30. How to pass a variable to a IN clause?

Leave a Comment