title: iSeries IDB2 - Declaring variables in stored procedure that returns a resultset
body: <p>I am trying to declare and set the value of a variable inside a stored procedure that returns a result set via a cursor. I am doing this because I plan for the variable to assist in filtering the results. </p>
  
  <p>When I my statement with just the cursor everything works well. Here's the code for that:</p>
  
  <pre><code>CREATE OR REPLACE PROCEDURE MYLIB.MYTEST()
  DYNAMIC RESULT SETS 1
  LANGUAGE SQL 
  SPECIFIC MYTEST 
  NOT DETERMINISTIC 
  READS SQL DATA 
  CALLED ON NULL INPUT 
  
  BEGIN
  
  DECLARE C1 CURSOR WITH RETURN FOR 
  SELECT * FROM SOMELIB.SOMEFILE 
  FETCH FIRST 100 ROWS ONLY;
  OPEN C1; 
  END; 
  </code></pre>
  
  <p>When I try to declare and set the variable as such I get an error. Here's the code: </p>
  
  <pre><code>CREATE OR REPLACE PROCEDURE MYLIB.MYTEST()
  DYNAMIC RESULT SETS 1
  LANGUAGE SQL 
  SPECIFIC MYTEST 
  NOT DETERMINISTIC 
  READS SQL DATA 
  CALLED ON NULL INPUT 
  
  BEGIN
  
  DECLARE SOMENAME VARCHAR(10);
  SET SOMENAME = 'JOHN';
  
  DECLARE C1 CURSOR WITH RETURN FOR 
  SELECT * FROM SOMELIB.SOMEFILE WHERE LASTNAME = SOMENAME
  FETCH FIRST 100 ROWS ONLY;
  OPEN C1; 
  END; 
  </code></pre>
  
  <p>The error I receive is the following:</p>
  
  <pre><code>SQL State: 42601
  Vendor Code: -104
  Message: [SQL0104] Token C1 was not valid. Valid tokens: GLOBAL. 
  Cause . . . . . :   A syntax error was detected at token C1.  
  Token C1 is not a valid token.  A partial list of valid tokens is GLOBAL.    
  This list assumes that the statement is correct up to the token.  
  The error may be earlier in the statement, but the syntax of the statement 
  appears to be valid up to this point. Recovery  . . . :   
  Do one or more of the following and try the request again: -- 
  Verify the SQL statement in the area of the token C1. Correct the statement.
  The error could be a missing comma or quotation mark, it could be a
  misspelled word, or it could be related to the order of clauses. -- 
  If the error token is &lt;END-OF-STATEMENT&gt;, correct the SQL statement 
  because it does not end with a valid clause.
  </code></pre>
  
  <p>Can anyone tell me the correct syntax for declaring and setting a variable on a stored procedure that returns a result set? Is it even possible?</p>