I've got this little code example where I call a procedure, which is taking 4 arguments:
DECLARE @tmp BIT
SET @tmp = 0
IF @tmp = 1
call __insert_to_notifications_table('foo', '1234', '5678', 'Some comment')
ELSE
print 'Path, which is choosen'
When running this, I'm getting
Syntaxerror at 'call' in line 4
SQLCODE=-131, ODBC 3-Status="42000"
If I run
call __insert_to_notifications_table('foo', '1234', '5678', 'Some comment')
directly, it is working fine. (the procedure is really called with double __ at the beginning; Also when renaming it's not working)
When running the code like this:
DECLARE @tmp BIT
SET @tmp = 0
IF @tmp = 1
print 'other path'
ELSE
print 'Path, which is choosen'
it seems to work. So I would hunt the issue down to the call of call.
What did I wrong here?
Best Answer
I think this is caused by a confusion between Watcom-SQL and T-SQL syntaxes.
Because you use CALL (rather than EXEC) in the first example the database engine will assume that the code is in Watcom SQL. (There is a PRINT statement later but the decision will have been made before then).
In the second example the command PRINT is only valid in T-SQL and the whole code segment is valid T-SQL so the database engine will assume that all the code is T-SQL and the THEN is not needed.
If I'm right then this should work (untested in Watcom-SQL):
For the differences in SQL Anywhere support for various SQL dialects see: http://dcx.sybase.com/index.html#1200/en/dbusage/ug-sql-compat.html
Update - this batch runs fine in dbISQL:
(I've added a BEGIN and END to give the DECLARE a context & used a built-in procedure rather than your user defined one) Tested in 10.0.1.4310 & 16.0.0.1535. If I omit the THEN I get the same error as you were getting.
A pure T-SQL version would be:
which also works on both 10.0.1.4310 & 16.0.0.1535. Don't forget that if you use T-SQL EXEC the parameters have to be specified individually (not in brackets):