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?

You didn't specify IN | OUT | INOUT for MYVAR.
So IN(default) was assumed.

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

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?

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.

In order to address those conditions, you can put the cursor declaration into a nested compound: