devolver result set o select en mysql con funcion o procedimiento almacenado ademas sql server

2
 


In MySQL, a function cannot return a table. You would have to use a stored procedure for that. – Dmytro Shevchenko Nov 6 '12 at 13:11 


4
 


What do you mean with "In SQL it's working fine?". You are using SQL.... – a_horse_with_no_name Nov 6 '12 at 13:28

************************

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  

*********************************

As per documentation on 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.

************

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 2 '14 at 6: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 2 '14 at 6:39 can you please see this error – Ankit Aranya May 2 '14 at 6:40 delimiter was not defined. Answer updated. Please check. – Ravinder Reddy May 2 '14 at 6:48 @AnkitAranya: Accepting Answers: How does it work? – Ravinder Reddy May 2 '14 at 7:25

*********

EN SQL SERVER

http://stackoverflow.com/questions/5604927/how-to-return-a-table-from-a-stored-procedure