How to "select" inside a stored procedure or a UDF in DB2 LUW?

Question

I believe this question is very trivial. I' unable to select rows inside a stored procedure that I defined, nor inside a UDF. Here is the syntax that I used:

Create Or Replace Procedure GenerateSequence(
In InternalUnitID SmallInt,
In ObjectTypeID SmallInt)

Language SQL
Dynamic Result Sets 1
Not Deterministic
No External Action
Modifies SQL Data
Commit On Return Yes
Begin
Select  Number
From    Sequence
Where   InternalUnit=InternalUnitID
    And ObjectType=ObjectTypeID;
End

If I try to create the above procedure (by putting the definition in a SQL file and running it). I get the following error message: DB21034E The command was processed as an SQL statement because it was not a valid Command Line Processor command. During SQL processing it returned:

SQL0104N  An unexpected token "Select Number  From Sequence  Where Intern" was
found following "n Return Yes Begin  ".  Expected tokens may include: "". 
LINE NUMBER=21.  SQLSTATE=42601

Any clue what can be the cause of this problem?

My environment is DB2 10.5 Express on Windows.

Answer 1

My problem was that I needed to use a cursor in order to return the result set to the caller of the stored procedure.

References:

CREATE PROCEDURE (SQL) statement

Compound SQL (compiled) statement