Results 1 to 5 of 5
  1. #1
    Join Date
    Oct 2008
    Posts
    19

    Unanswered: declaring/setting variables

    Hi,

    Can someone tell me what I'm doing wrong here as it appears pretty simple but it just doesn't work! I have the following stored proc:

    CREATE PROCEDURE MYPROC(MYVAR VARCHAR10))
    ....
    BEGIN
    SET MYVAR = LCASE(MYVAR); --THIS LINE DOESN'T WORK
    DECLARE C1 CURSOR FOR
    SELECT * FROM TABLE
    OPEN C1;
    END @

    Can anyone help with this? Am I setting the variable in the wrong place?

  2. #2
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    CREATE PROCEDURE MYPROC(MYVAR VARCHAR10))
    ....
    You didn't specify IN | OUT | INOUT for MYVAR.
    So IN(default) was assumed.

    Please try:
    CREATE PROCEDURE MYPROC(INOUT MYVAR VARCHAR10))
    ....

  3. #3
    Join Date
    Oct 2008
    Posts
    19
    Hi, thanks for the reply.

    This still isn't working though.

    I still get an error saying:

    SQL0104N An unexpected token "<cursor declaration>" was found following "".
    Expected tokens may include: "<SQL statement>".

    The SQL is as above with the SET after the BEGIN but before the cursor declaration.

    Any ideas?

  4. #4
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    Order of declarations/statements in a Procedure are defined.

    Please see "SQL Reference Volume 2" ---> "Statements" ---> "Compound SQL (Procedure)"

    [label:]
    BEGIN
    [NOT ATOMIC | ATOMIC]
    [SQL-variable-declaration; | condition-declaration; | return-codes-declaration;]
    [statement-declaration;]
    [DECLARE-CURSOR-statement;]
    [handler-declaration;]
    [SQL-procedure-statement;]
    END
    [label]

    There is no “large bullets” between the elements in the syntax diagram.
    This means that each elements in the diagram can't change the sequence of them.

    You wrote a SET statement(SQL-procedure-statement) before DECLARE-CURSOR-statement.
    Last edited by tonkuma; 10-14-08 at 01:02.

  5. #5
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    In order to address those conditions, you can put the cursor declaration into a nested compound:
    Code:
    BEGIN
       SET MYVAR = LCASE(MYVAR);
       BEGIN
          DECLARE C1 CURSOR FOR
             SELECT * FROM TABLE
          OPEN C1;
       END;
    END
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •