Can cursor be used inside a while statement?

[Former Member]

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

[VolkerBarth]

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...

[VolkerBarth · Accepted solution]

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;

[Former Member · Accepted solution]

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?

[Former Member · Accepted solution]

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