473,699 Members | 2,008 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

Need Help: Error while creating Store Procedure

1 New Member
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 8133
docdiesel
297 Recognized Expert Contributor
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 New Member
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
259
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 retrieve records ( about 30 records returned ). However, when I test the webmethod from local IIS server by supplying the parameter and clicking the "invoke" button, 40 seconds past, I got http 500 internal server error . any idea ?
13
2138
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 created the backup device, associated it with a backup name etc., copied the file into the backup dir. When I run the RESTORE command, Query Analyzer tells me the database
11
2802
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 (strPubCity) for Publisher City. These two fields have a many-to-one relationship with tables, (tlkpPubName and tlkpPubCity) respectively. The lookup tables only have one field (strPubName and strPubCity), which is their primary key. I also have...
6
4991
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 for long running reports. When the processing is complete it uses crystal reports to load a template file, populate it, and then export it to a PDF. It works fine so far....
0
1949
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 custom Store Procedure. I have Article table and ArticleLanguage table. One record from Article (Id, Position, StatusId) table is the same for all languages and in ArticleLanguage (Id, LanguageId, ArticleId, Name) table I have only article names...
0
3188
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 fine. Now we decided to move from mainframe IMS-DB2 to Windows 2003 server-DB2 UDB for LUW 9.5.
2
1488
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 information. The steps for entering inventory information are: (1) select an existing inventory item from the Select Item ComboBox control, (2) enter the wholesale cost of the item and quantity in stock, (3) click the Inventory menu, Add to Item Listing sub...
2
1763
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 library or path "...tine/sqlproc/NDB_PROD/CRS/P6510378", function "*" which cannot be accessed. Reason code: "4". SQLSTATE=42724
6
4186
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 working or not. When I submit after entering the data, an error comes. Please help me to solve this problem. Thanks
0
8697
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look ! Part I. Meaning of...
1
8929
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
0
8891
tracyyun
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
0
5878
by: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert into image. Globals.ThisAddIn.Application.ActiveDocument.Select();...
0
4380
by: TSSRALBI | last post by:
Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
0
4634
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
1
3061
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
2
2357
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.
3
2013
bsmnconsultancy
by: bsmnconsultancy | last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...

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.