Page 1 of 1

Stored Procedures Help

Posted: Mon Apr 21, 2008 10:15 am
by iceangel89
i created a stored procedure

Code: Select all

CREATE PROCEDURE GET_USER_ACL_BY_USERNAME (IN USR VARCHAR(255)) 
   BEGIN
   SELECT ACL.Action, ACL.Path FROM ACL
   WHERE ACL.UserGroup
   IN (
     SELECT Usergroup.GroupID
     FROM Users, UserGroup, Privilages
     WHERE Users.UserID = Privilages.UserID
     AND Usergroup.GroupID = Privilages.GroupID
     AND Users.UserName = USR
   );
   END|
then when i CALL it,

Code: Select all

CALL GET_USER_ACL_BY_USERNAME ('emp1')
it says
#1312 - PROCEDURE employeemanager.GET_USER_ACL_BY_USERNAME can't return a result set in the given context
whats wrong? how can i fix this? i just started stored procedures... and recommendations for Stored procedures resources at the same time?

Re: Stored Procedures Help

Posted: Tue Apr 22, 2008 10:11 am
by VladSun
http://dev.mysql.com/doc/refman/5.0/en/ ... nnect.html
CLIENT_MULTI_RESULTS
Tell the server that the client can handle multiple result sets from multiple-statement executions or stored procedures. This is automatically set if CLIENT_MULTI_STATEMENTS is set. See the note following this table for more information about this flag.
If your program uses the CALL SQL statement to execute stored procedures that produce result sets, you must set the CLIENT_MULTI_RESULTS flag, either explicitly, or implicitly by setting CLIENT_MULTI_STATEMENTS when you call mysql_real_connect(). This is because each such stored procedure produces multiple results: the result sets returned by statements executed within the procedure, as well as a result to indicate the call status.