title: Informix SQL Execute Function Inside FOREACH Cursor
body: <p>Informix versions 11.70 - 12.10</p>
  
  <p>Trying to create a Stored Procedure to get the Session ID of the user that has locked a table, and then Execute a built-in Function using that Session ID. I don't write too many procedures in Informix.</p>
  
  <p>Getting a syntax error around WITH RESUME.</p>
  
  <p>Any help?</p>
  
  <pre><code>CREATE PROCEDURE sp_kill_lock ()
  
  RETURNING
    INT as User_Session
  ;
  
  -- declaration of variables
  DEFINE rsUser_Session INT;
  
  -- SELECT each User Session that has a lock on a table
  SELECT DISTINCT s.sid AS User_Session
  FROM sysmaster:syslocks l, sysmaster:syssessions s 
  WHERE s.sid = l.owner 
     AND dbsname &lt;&gt; 'sysmaster' 
  ORDER BY 1 
  INTO TEMP tempSession;
  
  --Cursor
  FOREACH 
     SELECT User_Session
     INTO rsUser_Session
     FROM tempSession
  
  RETURN
  rsUser_Session;
  
  --kill the session returned
  EXECUTE FUNCTION sysadmin:task("onmode","z",rsUser_Session)
  
  WITH RESUME
  ;
  
  END FOREACH
  END PROCEDURE;
  
  
   -- Permissions for routine "sp_kill_lock"
   grant execute on function sp_kill_lock to 'public';
  </code></pre>
  
  <p>I changed the code, and at least I'm not getting a syntax error anymore. Now it says the function is returning too many values:</p>
  
  <pre><code>CREATE PROCEDURE sp_kill_lock ()
  
  RETURNING
    INT as User_Session
  ;
  
  -- declaration of variables
  DEFINE rsUser_Session INT;
  
  -- SELECT each User Session that has a lock on a table
  SELECT DISTINCT s.sid AS User_Session
  FROM sysmaster:syslocks l, sysmaster:syssessions s 
  WHERE s.sid = l.owner 
     AND dbsname &lt;&gt; 'sysmaster' 
  ORDER BY 1 
  INTO TEMP tempSession;
  
  --Cursor
  FOREACH 
     SELECT User_Session
     INTO rsUser_Session
     FROM tempSession
  
  --removed this RETURN
  --RETURN
  --rsUser_Session;
  
  --kill the session returned
  EXECUTE FUNCTION sysadmin:admin("onmode","z",rsUser_Session);
  
  --removed the WITH RESUME
  
  END FOREACH
  END PROCEDURE;
  </code></pre>
  
  <p>For testing a locked table:</p>
  
  <pre><code>BEGIN WORK;
  LOCK TABLE mytable IN EXCLUSIVE MODE
  </code></pre>