473,379 Members | 2,646 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,379 software developers and data experts.

Need Help: Error while creating Store Procedure

1
I am not able to create following store procedure.
Expand|Select|Wrap|Line Numbers
  1. CREATE PROCEDURE DBSAMBA.InsertDeleteBatch(OUT norows INT )
  2. RESULT SETS 1
  3. LANGUAGE SQL  
  4.  BEGIN part1
  5.    DECLARE TOTAL_LEFT INT DEFAULT 0;
  6.    SELECT COUNT(*)INTO TOTAL_LEFT FROM DBSAMBA.REPORTEDTRANSACTION_S;
  7.    WHILE (TOTAL_LEFT > 0) 
  8.    DO 
  9.       BEGIN part2
  10.  
  11.          INSERT INTO DBSAMBA.REPORTEDTRANSACTION_D(
  12.             TRANSACTION_REFERENCE,
  13.             VERSION,
  14.             CDM 
  15.          )(      
  16.          SELECT TRANSACTION_REFERENCE,
  17.             VERSION,
  18.             CDM 
  19.          FROM DBSAMBA.REPORTEDTRANSACTION_S 
  20.          FETCH FIRST 1000 ROWS ONLY);
  21.  
  22.         BEGIN part3
  23.         DECLARE Trans_Ref VARCHAR(20);   
  24.     DECLARE EXIT HANDLER FOR NOT FOUND SET norows = 1000;
  25.      DECLARE c1 CURSOR 
  26.             FOR 
  27.                SELECT TRANSACTION_REFERENCE 
  28.                FROM DBSAMBA.REPORTEDTRANSACTION_S 
  29.                FETCH FIRST 1000 ROWS ONLY;
  30.  
  31.  
  32.  
  33.          SET norows=0;
  34.           OPEN c1;
  35.  
  36.           while norows <1000
  37.               FETCH c1 INTO Trans_Ref;
  38.               DELETE 
  39.               FROM DBSAMBA.REPORTEDTRANSACTION_S
  40.               WHERE  TRANSACTION_REFERENCE =Trans_Ref;
  41.  
  42.           END WHILE;
  43.          close c1;  
  44.          END part3;   
  45.  
  46.          COMMIT;
  47.          SELECT COUNT(*)INTO TOTAL_LEFT 
  48.          FROM DBSAMBA.REPORTEDTRANSACTION_S;
  49.       END part2;
  50.    END WHILE;
  51. END part1;
  52.  

I get following error :-

Expand|Select|Wrap|Line Numbers
  1. REATE PROCEDURE DBSAMBA.PROC1(OUT norows INT )
  2. LANGUAGE SQL  
  3. BEGIN  
  4.    DECLARE TOTAL_LEFT INT DEFAULT 0
  5. DB21034E  The command was processed as an SQL statement because it was not a 
  6. valid Command Line Processor command.  During SQL processing it returned:
  7. SQL0104N  An unexpected token "END-OF-STATEMENT" was found following "L_LEFT 
  8. INT DEFAULT 0".  Expected tokens may include:  "<psm_semicolon>".  LINE 
  9. NUMBER=4.  SQLSTATE=42601
  10.  
  11. SELECT COUNT(*)INTO TOTAL_LEFT FROM DBSAMBA.REPORTEDTRANSACTION_S
  12. SQL0206N  "TOTAL_LEFT" is not valid in the context where it is used.  
  13. SQLSTATE=42703
  14.  
  15. 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)
  16. DB21034E  The command was processed as an SQL statement because it was not a 
  17. valid Command Line Processor command.  During SQL processing it returned:
  18. SQL0104N  An unexpected token "WHILE (TOTAL_LEFT > 0)  DO BEGIN PART2" was 
  19. found following "BEGIN-OF-STATEMENT".  Expected tokens may include:  
  20. "<space>".  SQLSTATE=42601
  21.  
  22. BEGIN PART3 DECLARE Trans_Ref VARCHAR(20)
  23. DB21034E  The command was processed as an SQL statement because it was not a 
  24. valid Command Line Processor command.  During SQL processing it returned:
  25. SQL0104N  An unexpected token "BEGIN PART3" was found following 
  26. "BEGIN-OF-STATEMENT".  Expected tokens may include:  "<create_proc>".  
  27. SQLSTATE=42601
  28.  
  29. DECLARE EXIT HANDLER FOR NOT FOUND SET norows = 1000
  30. DB21034E  The command was processed as an SQL statement because it was not a 
  31. valid Command Line Processor command.  During SQL processing it returned:
  32. SQL0104N  An unexpected token "<space>" was found following "EXIT".  Expected 
  33. tokens may include:  "HANDLER".  SQLSTATE=42601
  34.  
  35. DECLARE c1 CURSOR FOR SELECT TRANSACTION_REFERENCE FROM DBSAMBA.REPORTEDTRANSACTION_S FETCH FIRST 1000 ROWS ONLY
  36. DB20000I  The SQL command completed successfully.
  37.  
  38. SET norows=0
  39. DB21034E  The command was processed as an SQL statement because it was not a 
  40. valid Command Line Processor command.  During SQL processing it returned:
  41. SQL0206N  "NOROWS" is not valid in the context where it is used.  
  42. SQLSTATE=42703
  43.  
  44. OPEN c1
  45. DB20000I  The SQL command completed successfully.
  46.  
  47. while norows < 1000 FETCH c1 INTO Trans_Ref
  48. DB21034E  The command was processed as an SQL statement because it was not a 
  49. valid Command Line Processor command.  During SQL processing it returned:
  50. SQL0104N  An unexpected token "while norows < 1000" was found following 
  51. "BEGIN-OF-STATEMENT".  Expected tokens may include:  "<create_proc>".  
  52. SQLSTATE=42601
  53.  
  54. DELETE FROM DBSAMBA.REPORTEDTRANSACTION_S WHERE  TRANSACTION_REFERENCE =Trans_Ref
  55. DB21034E  The command was processed as an SQL statement because it was not a 
  56. valid Command Line Processor command.  During SQL processing it returned:
  57. SQL0206N  "TRANS_REF" is not valid in the context where it is used.  
  58. SQLSTATE=42703
  59.  
  60. END WHILE
  61. DB21034E  The command was processed as an SQL statement because it was not a 
  62. valid Command Line Processor command.  During SQL processing it returned:
  63. SQL0104N  An unexpected token "END-OF-STATEMENT" was found following "END 
  64. WHILE".  Expected tokens may include:  "JOIN <joined_table>".  SQLSTATE=42601
  65.  
  66. close c1
  67. DB21030E  The cursor "C1" has not been opened.
  68.  
  69. END PART3
  70. DB21034E  The command was processed as an SQL statement because it was not a 
  71. valid Command Line Processor command.  During SQL processing it returned:
  72. SQL0104N  An unexpected token "END-OF-STATEMENT" was found following "END 
  73. PART3".  Expected tokens may include:  "JOIN <joined_table>".  SQLSTATE=42601
  74.  
  75. COMMIT
  76. DB20000I  The SQL command completed successfully.
  77.  
  78. SELECT COUNT(*)INTO TOTAL_LEFT FROM DBSAMBA.REPORTEDTRANSACTION_S
  79. SQL0206N  "TOTAL_LEFT" is not valid in the context where it is used.  
  80. SQLSTATE=42703
  81.  
  82. END PART2
  83. DB21034E  The command was processed as an SQL statement because it was not a 
  84. valid Command Line Processor command.  During SQL processing it returned:
  85. SQL0104N  An unexpected token "END-OF-STATEMENT" was found following "END 
  86. PART2".  Expected tokens may include:  "JOIN <joined_table>".  SQLSTATE=42601
  87.  
  88. END WHILE
  89. DB21034E  The command was processed as an SQL statement because it was not a 
  90. valid Command Line Processor command.  During SQL processing it returned:
  91. SQL0104N  An unexpected token "END-OF-STATEMENT" was found following "END 
  92. WHILE".  Expected tokens may include:  "JOIN <joined_table>".  SQLSTATE=42601
  93.  
  94. END
  95. DB21034E  The command was processed as an SQL statement because it was not a 
  96. valid Command Line Processor command.  During SQL processing it returned:
  97. SQL0104N  An unexpected token "END-OF-STATEMENT" was found following "END".  
  98. Expected tokens may include:  "JOIN <joined_table>".  SQLSTATE=42601
  99.  
  100. SQL0104N  An unexpected token "END-OF-STATEMENT" was found following "END".  Expected tokens may include:  "JOIN <joined_table>                              ".
  101.  
  102. Explanation: 
  103.  
  104. A syntax error in the SQL statement or the input command string for the
  105. SYSPROC.ADMIN_CMD procedure was detected at the specified token
  106. following the text "<text>". The "<text>" field indicates the 20
  107. characters of the SQL statement or the input command string for the
  108. SYSPROC.ADMIN_CMD procedure that preceded the token that is not valid.
  109.  
  110. As an aid, a partial list of valid tokens is provided in the SQLERRM
  111. field of the SQLCA as "<token-list>". This list assumes the statement is
  112. correct to that point.
  113.  
  114. The statement cannot be processed.
  115.  
  116. User response: 
  117.  
  118. Examine and correct the statement in the area of the specified token.
  119.  
  120. sqlcode: -104
  121.  
  122. sqlstate: 42601
  123.  
  124.  
Jun 18 '08 #1
2 8029
docdiesel
297 Expert 100+
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
Jun 20 '08 #2
tensi4u
11
What's the delimiter you tried? If you use ';', try it again with another one such as '@'.

Hopefully helps you.

Thank you.

Sangjin
Jun 20 '08 #3

Sign in to post your reply or Sign up for a free account.

Similar topics

2
by: TK2828 | last post by:
Hi, Can anyone help me out on this ? from my web service I need to call a store procedure to fill a dataset which will be returned to client. When I test my store procedure, it takes 90sec to...
13
by: EggsAckley | last post by:
Hi: I have a file that I have been told is a SQL Server backup from a server somewhere. The file is about 200MB in size I am trying to create the database on my local server using RESTORE. I...
11
by: my-wings | last post by:
I think I've painted myself into a corner, and I'm hoping someone can help me out. I have a table of books (tblBooks), which includes a field (strPubName) for Publisher Name and another field...
6
by: James Radke | last post by:
Hello, I have a multithreaded windows NT service application (vb.net 2003) that I am working on (my first one), which reads a message queue and creates multiple threads to perform the processing...
0
by: halex | last post by:
Hello, I am having deadlock problem when I have a lot of visitors on my website at the same time. I am using NetTiers templates to generate C# classes for accessing DB layer and problem is in my...
0
by: SOI_0152 | last post by:
Hi all! Happy New Year 2008. Il hope it will bring you love and happyness I'm new on this forum. I wrote a stored procedure on mainframe using DB2 7.1.1 and IBM language c. Everything works...
2
by: redfog | last post by:
I need help writing some code for this procedure: Inventory Add to Item Listing menu item (15 points). Application users will use this menu as part of the process of entering inventory...
2
by: Ramchandra | last post by:
Hi, i am getting error while executing the store procedure db2 "CALL CRS.UPDATE_DEAD_RECORD2(')" SQL0444N Routine "*_RECORD2" (specific name "SQL080221165103852") is implemented with code in...
6
by: nzkks | last post by:
Hi I am using these: ASP.Net 2.0 with VB.Net, Visual Studio 2005, SQL Server 2005 I suspect, there is something missing in BLL class. I created the ASP.Net form also and checked whether it is...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...
0
by: ryjfgjl | last post by:
In our work, we often need to import Excel data into databases (such as MySQL, SQL Server, Oracle) for data analysis and processing. Usually, we use database tools like Navicat or the Excel import...
0
by: aa123db | last post by:
Variable and constants Use var or let for variables and const fror constants. Var foo ='bar'; Let foo ='bar';const baz ='bar'; Functions function $name$ ($parameters$) { } ...
0
by: ryjfgjl | last post by:
If we have dozens or hundreds of excel to import into the database, if we use the excel import function provided by database editors such as navicat, it will be extremely tedious and time-consuming...
0
by: ryjfgjl | last post by:
In our work, we often receive Excel tables with data in the same format. If we want to analyze these data, it can be difficult to analyze them because the data is spread across multiple Excel files...
1
by: Sonnysonu | last post by:
This is the data of csv file 1 2 3 1 2 3 1 2 3 1 2 3 2 3 2 3 3 the lengths should be different i have to store the data by column-wise with in the specific length. suppose the i have to...
0
by: Hystou | last post by:
There are some requirements for setting up RAID: 1. The motherboard and BIOS support RAID configuration. 2. The motherboard has 2 or more available SATA protocol SSD/HDD slots (including MSATA, M.2...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.