1

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?

  • Issue sounds similar, but solution seems not to fit. – frlan Aug 5 '14 at 13:08
  • Could you try a BEGIN ... END block around the CALL? – RLF Aug 5 '14 at 15:31
  • Already done. No change. – frlan Aug 5 '14 at 16:04
  • Are you running this in dbISQL or something else? – Justin W Aug 6 '14 at 10:09
2

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):

DECLARE @tmp BIT;
SET @tmp = 0;
IF @tmp = 1
   THEN  call __insert_to_notifications_table('foo', '1234', '5678', 'Some comment')
ELSE
    MESSAGE 'Path, which is chosen' TO CLIENT
END IF;

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:

BEGIN
  DECLARE @tmp BIT;
  SET @tmp = 0;
  IF @tmp = 1
       THEN call dbo.sa_info()
  ELSE
      MESSAGE 'Path, which is chosen' TO CLIENT
  END IF;
END

(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:

BEGIN
  DECLARE @tmp BIT
  SET @tmp = 0
  IF @tmp = 1
   EXEC dbo.sa_info
  ELSE
   PRINT 'Path, which is choosen'
END

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):

[ EXECUTE ] | [ EXEC ][ @return_status = ] [creator.]procedure_name [ argument, ... ]
argument :
 [ @parameter-name = ] expression
| [ @parameter-name = ] @variable [ output ]
  • Based on the snippet I did a version which is actually running in T-SQL using exec and no parentheses. Unfortunately the example above is not working. Still some time need. – frlan Aug 6 '14 at 11:39
  • Hi - I've updated my answer with an example using a built-in procedure. – Justin W Aug 7 '14 at 12:30

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.