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
31.3k312458674
accept rate: 32%

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:

×18

question asked: 09 Nov '12, 13:59

question was seen: 1,355 times

last updated: 09 Nov '12, 14:56