The forum will experience an outage sometime between February 10 at 7:00pm EST and February 12 at 11:59 EST for installation of security updates. The actual time and duration of the outage are unknown but attempts will be made to minimize the downtime. We apologize for any inconvenience.

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
29.3k287438644
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:

×16

question asked: 09 Nov '12, 13:59

question was seen: 1,126 times

last updated: 09 Nov '12, 14:56