Search code examples
sybasesqlanywheresap-iq

Sybase: Very strange behaviour on WHILE statement when using ';'

I encounter a very strange problem in using WHILE in sybase. Why this code execute :

BEGIN
WHILE 'toto' = 'titi'
BEGIN
    DECLARE @val int
    SELECT * FROM randomtable1
    SELECT * FROM randomtable2
    END
END

And not this one ?

BEGIN
DECLARE @val INT;

WHILE 'toto' = 'titi'
BEGIN
    DECLARE @val int
    SELECT * FROM randomtable1
    SELECT * FROM randomtable2  
END
END

In fact I noticed that the moment I use a ';' in my script, in or out of the WHILE statement, Sybase returns a syntax error near the BEGIN just after the WHILE. I have read and reread the sybase documentation and search and I don't understand what happen here.

The examples I give are simplification that reproduce the error I have, but this is not the real script I'm trying to do.

Please note that I'm in internship and I'm discovering Sybase for the first time. I'm scared I have missed something obvious.

Any idea ?

Thanks in advance

Solution

  • It looks like you are mixing the Watcom SQL (original, native SQL Anywhere dialect) and Transact-SQL (from SYbase ASE, partly also supported by SQL Anywhere). You cannot mix the two in the same SQL batch.

    • Watcom uses a ';' as statement delimiter, Transact-SQL will throw an error when it sees a ';'

    • Transact-SQL uses BEGIN-END for statement grouping, as you;re doing with the WHILE. However, in Watcom, BEGIN-END does not group statements but is a unit of exception handling.

    • In Watcom, use the 'WHILE ... LOOP ... END LOOP;' syntax