Question about Sql Procedure

Asha Jijen

Question about Sql Procedure
Can someone help. I am new to Db2 and have a question regarding "CREATE
PROCEDURE" command.
I have Db2 7.1 and the following script I try to execute

CREATE PROCEDURE aaww()
BEGIN
DECLARE i INTEGER DEFAULT 1;
ins_loop:
LOOP IF i>100 THEN
LEAVE ins_loop;
ELSEIF i<=100 THEN
ITERATE ins_loop;
END IF;
INSERT INTO TAB1 VALUES (i,'FIRST VALUES');
SET i = i + 1;
END LOOP;
END@

and getting error
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 "BEGIN" was found following "ATE PROCEDURE
aaww()". Expected tokens may include: "<proc_attribute_list>". LINE
NUMBER=1. SQLSTATE=42601
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 "i" was found following "DECLARE ". Expected
tokens may include: "JOIN <joined_table>". SQLSTATE=42601
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 ": LOOP IF i>100 THEN LEAVE ins_loop;" was
found following "ins_loop". Expected tokens may include: "<space>".
SQLSTATE=42601
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 "i" was found following "ELSEIF ". Expected
tokens may include: "JOIN <joined_table>". SQLSTATE=42601
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 "IF" was found following "END ". Expected
tokens may include: "JOIN <joined_table>". SQLSTATE=42601
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 ";" was found following "S (I,'FIRST
VALUES')". Expected tokens may include: "END-OF-STATEMENT".
SQLSTATE=42601
DB21034E The command was processed as an SQL statement because it was not a
valid Command Line Processor command. During SQL processing it returned:
SQL0206N "I" is not valid in the context where it is used. SQLSTATE=42703
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 "LOOP" was found following "END ". Expected
tokens may include: "JOIN <joined_table>". SQLSTATE=42601
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 "END-OF-STATEMENT" was found following "END@".
Expected tokens may include: "JOIN <joined_table>". SQLSTATE=42601

Thanks
Asha





=======================






Benjamin Reches

Re: Question about Sql Procedure
(in response to Asha Jijen)
Asha,

Try labeling the BEGIN block and specify the label on the END statement.

Ben

Asha Jijen wrote:

> Can someone help. I am new to Db2 and have a question regarding "CREATE
> PROCEDURE" command.
> I have Db2 7.1 and the following script I try to execute
>
> CREATE PROCEDURE aaww()
> BEGIN
> DECLARE i INTEGER DEFAULT 1;
> ins_loop:
> LOOP IF i>100 THEN
> LEAVE ins_loop;
> ELSEIF i<=100 THEN
> ITERATE ins_loop;
> END IF;
> INSERT INTO TAB1 VALUES (i,'FIRST VALUES');
> SET i = i + 1;
> END LOOP;
> END@
>
> and getting error
> 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 "BEGIN" was found following "ATE PROCEDURE
> aaww()". Expected tokens may include: "<proc_attribute_list>". LINE
> NUMBER=1. SQLSTATE=42601
> 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 "i" was found following "DECLARE ". Expected
> tokens may include: "JOIN <joined_table>". SQLSTATE=42601
> 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 ": LOOP IF i>100 THEN LEAVE ins_loop;" was
> found following "ins_loop". Expected tokens may include: "<space>".
> SQLSTATE=42601
> 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 "i" was found following "ELSEIF ". Expected
> tokens may include: "JOIN <joined_table>". SQLSTATE=42601
> 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 "IF" was found following "END ". Expected
> tokens may include: "JOIN <joined_table>". SQLSTATE=42601
> 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 ";" was found following "S (I,'FIRST
> VALUES')". Expected tokens may include: "END-OF-STATEMENT".
> SQLSTATE=42601
> DB21034E The command was processed as an SQL statement because it was not a
> valid Command Line Processor command. During SQL processing it returned:
> SQL0206N "I" is not valid in the context where it is used. SQLSTATE=42703
> 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 "LOOP" was found following "END ". Expected
> tokens may include: "JOIN <joined_table>". SQLSTATE=42601
> 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 "END-OF-STATEMENT" was found following "END@".
> Expected tokens may include: "JOIN <joined_table>". SQLSTATE=42601
>
> Thanks
> Asha
>
>
>
>
>
> =======================
>
>
>
>
>
>