Please be aware that the content in SAP SQL Anywhere Forum will be migrated to the SAP Community in June and this forum will be retired.

Hello all,

Does anyone know what's wrong with the following WHILE statement?

I am trying to write a script on Sybase Anywhere 12.0.1 to alter all integer columns to bigint columns. I wrote the script like below(this is a simplified version). When I ran it, it was always telling me there was a syntax error near 'begin' on line 8. If I took the 'begin ... end' out of the while statement, it would say there was a syntax error near 'fetch' on line 10. What's wrong with while statement? Is it because I have to use loop...end loop for cursor as I saw some examples?

1   BEGIN
2       DECLARE cur CURSOR FOR select table_name from SYSTAB;
3       DECLARE @tablename NCHAR(100);
4
5       open cur with hold;
6       fetch cur into @tablename;
7       WHILE @@fetch_status = 0
8       begin
9           -- do something with cursor
10          fetch cur into @tablename;
11      end
12       CLOSE int_cursor;
13  END

asked 09 Nov '12, 13:59

bravestone's gravatar image

bravestone
50236
accept rate: 0%

edited 09 Nov '12, 14:04

FWIW, if you are about to call a DDL statement inside the loop (say, ALTER TABLE), it is way easier to use a FOR loop - cf. this FAQ on a similar issue...

(09 Nov '12, 14:56) Volker Barth

I guess you're mixing Watcom-SQL and T-SQL dialects here.

As most of the code is Watcom-SQL, you should be able to simply adapt the WHILE loop to Watcom-SQL syntax:

WHILE @@fetch_status = 0 LOOP
   -- do something with cursor
   fetch cur into @tablename;
END LOOP;
permanent link

answered 09 Nov '12, 14:14

Volker%20Barth's gravatar image

Volker Barth
40.3k363552824
accept rate: 34%

Thanks for the quick answer! I changed it to while loop...end loop. It passed that point but it's now complaining a syntax error near 'close' on line 12. The 'int_cursor' should be 'cur'. Any idea?

(09 Nov '12, 14:22) bravestone

I missed a semicolon after END LOOP. That's it.

(09 Nov '12, 14:27) bravestone
Your answer
toggle preview

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text](http://url.com/ "title")
  • image?![alt text](/path/img.jpg "title")
  • numbered list: 1. Foo 2. Bar
  • to add a line break simply add two spaces to where you would like the new line to be.
  • basic HTML tags are also supported

Question tags:

×24

question asked: 09 Nov '12, 13:59

question was seen: 3,456 times

last updated: 09 Nov '12, 14:56