MySQL Stored Procedures not working with SELECT (basic question)
- by TMG
Hello,
I am using a platform (perfectforms) that requires me to use stored procedures for most of my queries, and having never used stored procedures, I can't figure out what I'm doing wrong. The following statement executes without error:
DELIMITER //
DROP PROCEDURE IF EXISTS test_db.test_proc//
CREATE PROCEDURE test_db.test_proc() SELECT 'foo'; //
DELIMITER ;
But when I try to call it using:
CALL test_proc();
I get the following error:
#1312 - PROCEDURE test_db.test_proc can't return a result set in the given context
I am executing these statements from within phpmyadmin 3.2.4, PHP Version 5.2.12 and the mysql server version is 5.0.89-community.
When I write a stored procedure that returns a parameter, and then select it, things work fine (e.g.):
DELIMITER //
DROP PROCEDURE IF EXISTS test_db.get_sum//
CREATE PROCEDURE test_db.get_sum(out total int)
BEGIN
SELECT SUM(field1) INTO total FROM test_db.test_table;
END //
DELIMITER ;
works fine, and when I call it:
CALL get_sum(@t); SELECT @t;
I get the sum no problem.
Ultimately, what I need to do is have a fancy SELECT statement wrapped up in a stored procedure, so I can call it, and return multiple rows of multiple fields. For now I'm just trying to get any select working.
Any help is greatly appreciated.
