How can we access tables through MySQL stored procedures?

We can access one or all the tables from the MySQL stored procedure. Following is an example in which we created a stored procedure that will accept the name of the table as a parameter and after invoking it, will produce the result set with all the details from the table.

Example

mysql> Delimiter //
mysql> Create procedure access(tablename varchar(30))
   -> BEGIN
   -> SET @X := CONCAT('Select * from',' ',tablename);
   -> Prepare statement from @X;
   -> Execute statement;
   -> END//
Query OK, 0 rows affected (0.16 sec)

Now invoke the procedure with the table name, we need to access, as its parameter.

mysql> Delimiter ;

mysql> Call access('student_info');
+------+---------+----------+------------+
| id   | Name    | Address  | Subject    |
+------+---------+----------+------------+
| 101  | YashPal | Amritsar | History    |
| 105  | Gaurav  | Jaipur   | Literature |
| 125  | Raman   | Shimla   | Computers  |
+------+---------+----------+------------+
3 rows in set (0.02 sec)
Query OK, 0 rows affected (0.04 sec)

Krantik Chavan

Published on 21-Feb-2018 10:43:07

• Related Questions & Answers
• How can we grant a user to access all stored procedures in MySQL?
• What is stored procedure and how can we create MySQL stored procedures?
• How can we see the list of stored procedures and stored functions in a particular MySQL database?
• How can we create MySQL stored procedures without ‘BEGIN’ and ‘END’?
• How can we see only the list of stored procedures in a particular MySQL database?
• How can we invoke MySQL stored procedure?
• What are stored procedures? How to call stored procedures using JDBC program?
• How can we see the list, along with other information, stored procedures in a particular MySQL database?
• Create a stored Procedures using MySQL Workbench?
• How can we see the list, along with complete information, of stored procedures in a particular MySQL database?
• How can we alter a MySQL stored procedure?
• How can we drop a MySQL stored procedure?
• How can we alter a MySQL stored function?
• How can we see MySQL temporary tables in the list of tables?
• How can we compare data in two MySQL tables?