-1

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.

Ahmed Mostafa Abdel-Baky
  • 89
  • 1
  • 8
  • 2
    Take a look at the manual. https://www.ibm.com/support/knowledgecenter/SSEPGG_10.5.0/com.ibm.db2.luw.sql.ref.doc/doc/r0008329.html?pos=2 To return a result set, you simple declare a cursor and open it prior to exiting the procedure. – Charles Nov 21 '16 at 20:27
  • number and sequence are both reserved words – Hogan Nov 21 '16 at 21:16
  • 1
    The hardest part of creating a SP for me was always getting the command line options correct for using a different batch terminator from `;` so I can include `;` in my SP code. – Hogan Nov 21 '16 at 21:20
  • http://www.ibm.com/support/knowledgecenter/SSEPGG_10.5.0/com.ibm.db2.luw.apdv.sqlpl.doc/doc/t0009220.html – Hogan Nov 21 '16 at 21:24
  • 1
    Just another manifestation of this: https://stackoverflow.com/questions/40699343/declaring-variable-in-db2-using-sql – mustaccio Nov 21 '16 at 21:56

1 Answers1

0

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

Ahmed Mostafa Abdel-Baky
  • 89
  • 1
  • 8