Need Help: Error while creating Store Procedure

[kya2]

I am not able to create following store procedure.

CREATE PROCEDURE DBSAMBA.InsertDeleteBatch(OUT norows INT )
RESULT SETS 1
LANGUAGE SQL  
 BEGIN part1
   DECLARE TOTAL_LEFT INT DEFAULT 0;
   SELECT COUNT(*)INTO TOTAL_LEFT FROM DBSAMBA.REPORTEDTRANSACTION_S;
   WHILE (TOTAL_LEFT > 0) 
   DO 
      BEGIN part2
       
         INSERT INTO DBSAMBA.REPORTEDTRANSACTION_D(
            TRANSACTION_REFERENCE,
            VERSION,
            CDM 
         )( 	 
         SELECT TRANSACTION_REFERENCE,
            VERSION,
            CDM 
         FROM DBSAMBA.REPORTEDTRANSACTION_S 
         FETCH FIRST 1000 ROWS ONLY);

        BEGIN part3
        DECLARE Trans_Ref VARCHAR(20);   
	DECLARE EXIT HANDLER FOR NOT FOUND SET norows = 1000;
	 DECLARE c1 CURSOR 
            FOR 
               SELECT TRANSACTION_REFERENCE 
               FROM DBSAMBA.REPORTEDTRANSACTION_S 
               FETCH FIRST 1000 ROWS ONLY;
                                   
       
          
         SET norows=0;
          OPEN c1;
          
          while norows <1000
              FETCH c1 INTO Trans_Ref;
              DELETE 
              FROM DBSAMBA.REPORTEDTRANSACTION_S
              WHERE  TRANSACTION_REFERENCE =Trans_Ref;
 
          END WHILE;
         close c1;  
         END part3;   
        
         COMMIT;
         SELECT COUNT(*)INTO TOTAL_LEFT 
         FROM DBSAMBA.REPORTEDTRANSACTION_S;
      END part2;
   END WHILE;
END part1;

I get following error :-

REATE PROCEDURE DBSAMBA.PROC1(OUT norows INT )
LANGUAGE SQL  
BEGIN  
   DECLARE TOTAL_LEFT INT DEFAULT 0
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 "L_LEFT 
INT DEFAULT 0".  Expected tokens may include:  "<psm_semicolon>".  LINE 
NUMBER=4.  SQLSTATE=42601

SELECT COUNT(*)INTO TOTAL_LEFT FROM DBSAMBA.REPORTEDTRANSACTION_S
SQL0206N  "TOTAL_LEFT" is not valid in the context where it is used.  
SQLSTATE=42703

WHILE (TOTAL_LEFT > 0)  DO BEGIN PART2 INSERT INTO DBSAMBA.REPORTEDTRANSACTION_D(TRANSACTION_REFERENCE, VERSION, CDM ) (SELECT TRANSACTION_REFERENCE, VERSION, CDM FROM DBSAMBA.REPORTEDTRANSACTION_S FETCH FIRST 1000 ROWS ONLY)
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 "WHILE (TOTAL_LEFT > 0)  DO BEGIN PART2" was 
found following "BEGIN-OF-STATEMENT".  Expected tokens may include:  
"<space>".  SQLSTATE=42601

BEGIN PART3 DECLARE Trans_Ref VARCHAR(20)
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 PART3" was found following 
"BEGIN-OF-STATEMENT".  Expected tokens may include:  "<create_proc>".  
SQLSTATE=42601

DECLARE EXIT HANDLER FOR NOT FOUND SET norows = 1000
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 "<space>" was found following "EXIT".  Expected 
tokens may include:  "HANDLER".  SQLSTATE=42601

DECLARE c1 CURSOR FOR SELECT TRANSACTION_REFERENCE FROM DBSAMBA.REPORTEDTRANSACTION_S FETCH FIRST 1000 ROWS ONLY
DB20000I  The SQL command completed successfully.

SET norows=0
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  "NOROWS" is not valid in the context where it is used.  
SQLSTATE=42703

OPEN c1
DB20000I  The SQL command completed successfully.

while norows < 1000 FETCH c1 INTO Trans_Ref
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 "while norows < 1000" was found following 
"BEGIN-OF-STATEMENT".  Expected tokens may include:  "<create_proc>".  
SQLSTATE=42601

DELETE FROM DBSAMBA.REPORTEDTRANSACTION_S WHERE  TRANSACTION_REFERENCE =Trans_Ref
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  "TRANS_REF" is not valid in the context where it is used.  
SQLSTATE=42703

END WHILE
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 
WHILE".  Expected tokens may include:  "JOIN <joined_table>".  SQLSTATE=42601

close c1
DB21030E  The cursor "C1" has not been opened.

END PART3
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 
PART3".  Expected tokens may include:  "JOIN <joined_table>".  SQLSTATE=42601

COMMIT
DB20000I  The SQL command completed successfully.

SELECT COUNT(*)INTO TOTAL_LEFT FROM DBSAMBA.REPORTEDTRANSACTION_S
SQL0206N  "TOTAL_LEFT" is not valid in the context where it is used.  
SQLSTATE=42703

END PART2
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 
PART2".  Expected tokens may include:  "JOIN <joined_table>".  SQLSTATE=42601

END WHILE
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 
WHILE".  Expected tokens may include:  "JOIN <joined_table>".  SQLSTATE=42601

END
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

SQL0104N  An unexpected token "END-OF-STATEMENT" was found following "END".  Expected tokens may include:  "JOIN <joined_table>                              ".

Explanation: 

A syntax error in the SQL statement or the input command string for the
SYSPROC.ADMIN_CMD procedure was detected at the specified token
following the text "<text>". The "<text>" field indicates the 20
characters of the SQL statement or the input command string for the
SYSPROC.ADMIN_CMD procedure that preceded the token that is not valid.

As an aid, a partial list of valid tokens is provided in the SQLERRM
field of the SQLCA as "<token-list>". This list assumes the statement is
correct to that point.

The statement cannot be processed.

User response: 

Examine and correct the statement in the area of the specified token.

sqlcode: -104

sqlstate: 42601

[docdiesel]

Hi,

which command or tool do you use when setting off the "create procedure" call? Did you try without "partx" after BEGIN and END?

Regards,

Bernd

[tensi4u]

What's the delimiter you tried? If you use ';', try it again with another one such as '@'.

Hopefully helps you.

Thank you.

Sangjin