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