13
CREATE FUNCTION myFunction(id INT) RETURNS TABLE  
BEGIN  
   RETURN SELECT * FROM board;  
END

This query gives following error:

1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'TABLE
Jonathan Leffler
  • 730,956
  • 141
  • 904
  • 1,278
Ankit Aranya
  • 930
  • 1
  • 10
  • 17

2 Answers2

40

As per documentation on loadable user defined functions in MySQL
you can only return values of type {STRING|INTEGER|REAL|DECIMAL}

CREATE [AGGREGATE] FUNCTION function_name RETURNS {STRING|INTEGER|REAL|DECIMAL}
    SONAME shared_library_name

If you want to read a select resultset you have to define a procedure but not function.

DELIMITER //

DROP PROCEDURE IF EXISTS myProcedure //

CREATE PROCEDURE 
  myProcedure( id INT )
BEGIN  
   SELECT * FROM board
     -- add where condition if required
    WHERE Col_name = id
   ;  
END 
//

DELIMITER ;

And you can call procedure like

call myProcedure( 6 )

That returns implicit objects based on the statements used in the procedure.

Also Refer to: Adding a Loadable Function

  • Functions can return string, integer, or real values and can accept arguments of those same types
Ravinder Reddy
  • 23,692
  • 6
  • 52
  • 82
  • CREATE PROCEDURE myProcedure( id INT ) BEGIN SELECT * FROM board WHERE `id` = id ; END //check the manual that corresponds to your MySQL server version for the right syntax to use near '' at line 5 – Ankit Aranya May 02 '14 at 06:36
  • CREATE PROCEDURE myProcedure( id INT ) BEGIN select * from board; END // check the manual that corresponds to your MySQL server version for the right syntax to use near '' at line 3 – Ankit Aranya May 02 '14 at 06:39
  • As we cannot use the result of SP in another SP, so there is no way if you want to do some calculation before returning table and then use the result in another SP? – Himalaya Garg Aug 14 '21 at 04:32
0

Stored procedures can, but stored functions cannot. If you perform an ordinary SELECT inside a stored procedure, the result set is returned directly to the client. You need to use the MySQL 4.1 (or higher) client/server protocol for this to work. This means that, for example, in PHP, you need to use the mysqli extension rather than the old mysql extension.

reference link: mySql docs

mkm123
  • 1
  • 1