How to return a resultset from StoredProcedure using MySql?

Question

I am writing a stored procedure using MySql which returns multiple rows using select statement.

My code is as below

drop procedure if exists GetAccounts; 
DELIMITER // 
CREATE PROCEDURE GetAccounts() 
BEGIN 
DECLARE rowcount int;
SET @resultset = (SELECT * from requests where STATUS = "FAILURE" ;
END // 
DELIMITER

Any examples of how to return a resultSet in storedProcedure?

Thanks Gendaful

score 8 · Accepted Answer · answered Oct 28 '12 at 01:30

8

DROP PROCEDURE IF EXISTS GetAccounts; 
DELIMITER // 
CREATE PROCEDURE GetAccounts() 
BEGIN 
DECLARE rowcount int;
    SELECT * from requests where STATUS = "FAILURE" ;
END // 
DELIMITER ;

answered Oct 28 '12 at 01:30

felipsmartins

13,269
4
48
56

1

Thanks, But I want to return the resultSet to a DAO. So, I believe, I have to use output parameter, right? – Gendaful Oct 28 '12 at 01:31
Are you using any language, PHP for instance? – felipsmartins Oct 28 '12 at 01:33
Ya, I am using hibernate to call this stored procedure and the expectation is this storedprocedure should return me the selected rows from the table. – Gendaful Oct 28 '12 at 01:36
But, I should declare the output variable right? otherwise how the list will be returned? – Gendaful Oct 28 '12 at 01:40
Not necessarily! SELECT statement in SP should returns rows when invoking procedure – felipsmartins Oct 28 '12 at 01:53

How to return a resultset from StoredProcedure using MySql?

1 Answers1