Question Re: Can cursor be used inside a while statement? in Technology Q&A

Can cursor be used inside a while statement?

Former Member — Fri, 09 Nov 2012 13:59:48 GMT

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

Re: Can cursor be used inside a while statement?

VolkerBarth — Fri, 09 Nov 2012 14:14:18 GMT

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;

Re: Can cursor be used inside a while statement?

Former Member — Fri, 09 Nov 2012 14:22:51 GMT

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?

Re: Can cursor be used inside a while statement?

Former Member — Fri, 09 Nov 2012 14:27:08 GMT

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

Re: Can cursor be used inside a while statement?

VolkerBarth — Fri, 09 Nov 2012 14:56:22 GMT

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