How to return table from MySQL function?

You cannot return table from MySQL function. The function can return string, integer, char etc. To return table from MySQL, use stored procedure, not function.

Let us first create a table −

mysql> create table DemoTable696 (
   Id int,
   Name varchar(100)
);
Query OK, 0 rows affected (0.77 sec)

Insert some records in the table using insert command −

mysql> insert into DemoTable696 values(100,'Mike');
Query OK, 1 row affected (0.14 sec)
mysql> insert into DemoTable696 values(101,'Sam');
Query OK, 1 row affected (0.17 sec)
mysql> insert into DemoTable696 values(102,'Adam');
Query OK, 1 row affected (0.14 sec)
mysql> insert into DemoTable696 values(103,'Carol');
Query OK, 1 row affected (0.20 sec)

Display all records from the table using select statement −

mysql> select *from DemoTable696;

This will produce the following output -

+------+-------+
| Id   | Name  |
+------+-------+
| 100  | Mike  |
| 101  | Sam   |
| 102  | Adam  |
| 103  | Carol |
+------+-------+
4 rows in set (0.00 sec)

Following is the query to return table from MySQL.

The stored procedure is as follows −

mysql> DELIMITER //
mysql> CREATE PROCEDURE getResultSet(studId int )
   BEGIN
      select *from DemoTable696 where Id=studId;
   END
   //
Query OK, 0 rows affected (0.14 sec)
mysql> DELIMITER ;

Now you can call the stored procedure using call command −

mysql> call getResultSet(103);

This will produce the following output -

+------+-------+
| Id   | Name  |
+------+-------+
| 103  | Carol |
+------+-------+
1 row in set (0.00 sec)
Query OK, 0 rows affected (0.01 sec)

AmitDiwan

Updated on: 21-Aug-2019

8K+ Views

Kickstart Your Career

Get certified by completing the course

Get Started