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?

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

1 Answer 1

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 ]
2
  • 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, 2014 at 11:39
  • Hi - I've updated my answer with an example using a built-in procedure.
    – Justin W
    Aug 7, 2014 at 12:30

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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