How to return the table using mysql Function

Question

I am using MySQL. I want to return the table using MySQL function. In SQL its working fine but not in MySQL. I attach my partial code

 DELIMITER $$

    CREATE FUNCTION myFunction() RETURNS @tmptable TABLE (item varchar(20))
    BEGIN  

        insert  into @tmptable(item) values('raja')     


        return
     END; $$

In MySQL, a function cannot return a table. You would have to use a stored procedure for that. — Dmytro Shevchenko
– Dmytro Shevchenko, Commented Nov 6, 2012 at 13:11
What do you mean with "In SQL it's working fine?". You are using SQL.... — user330315
– user330315, Commented Nov 6, 2012 at 13:28

Rahul Tripathi · Accepted Answer · 2012-11-06 13:12:01Z

9

Using functions you can not return a table.

However you can use stored procedure to return the table.

 DELIMITER $$

 CREATE DEFINER=`root`@`%` PROCEDURE `sp_Name`(OUT po_ErrMessage   VARCHAR(200))
 BEGIN
 DECLARE EXIT HANDLER FOR SQLEXCEPTION
 BEGIN
SET po_ErrMessage = 'Error in procedure sp_Name';
 END;

 SELECT * FROM table_name;
END

answered Nov 6, 2012 at 13:12

Rahul Tripathi

173k33 gold badges292 silver badges341 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Bharathi Over a year ago

Ya its working fine in procedure. Is there any way to return more than one row using MySQL function. If Possible means give example

Rahul Tripathi Over a year ago

You can check this:- stackoverflow.com/questions/10850731/… It will definitely help!!!

Collectives™ on Stack Overflow

How to return the table using mysql Function

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related