iSeries IDB2 - Declaring variables in stored procedure that returns a resultset

Question

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.

When I my statement with just the cursor everything works well. Here's the code for that:

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; 

When I try to declare and set the variable as such I get an error. Here's the 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; 

The error I receive is the following:

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 <END-OF-STATEMENT>, correct the SQL statement 
because it does not end with a valid clause.

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?

Answer 1

You need the all the DECLARE done before the executable code. Move the SET

CREATE OR REPLACE PROCEDURE MYLIB.MYTEST()
DYNAMIC RESULT SETS 1
LANGUAGE SQL 
SPECIFIC MYTEST 
NOT DETERMINISTIC 
READS SQL DATA 
CALLED ON NULL INPUT 

BEGIN

-- Declaration statements
DECLARE SOMENAME VARCHAR(10);
DECLARE C1 CURSOR WITH RETURN FOR 
SELECT * FROM SOMELIB.SOMEFILE WHERE LASTNAME = SOMENAME
FETCH FIRST 100 ROWS ONLY;

-- Executable statements 
SET SOMENAME = 'JOHN';
OPEN C1; 
END;