Beware Of The OUT Parameter

Question: Why doesn't SQL Anywhere raise a "Wrong number of parameters" exception for the following CALL?

The procedure originally had three parameters @px, @py and @pz. A fourth parameter @pb was added, but the CALL wasn't changed.

According to the docs this should have resulted in an error: "Procedure arguments can be assigned default values in the CREATE PROCEDURE statement, and missing parameters are assigned the default value. If no default is set, and an argument is not provided, an error is given."

...but, in this case, there's no error, just a wrong answer:

CREATE PROCEDURE p (
   IN    @px      INTEGER,
   IN    @pb      INTEGER,
   OUT   @py      INTEGER,
   OUT   @pz      INTEGER )
BEGIN
   SET @py = @px + 1;
   SET @pz = @pb + 99;
END;

BEGIN
DECLARE @x      INTEGER;
DECLARE @y      INTEGER;
DECLARE @z      INTEGER;

SET @x = 1;

CALL p ( @x, @y, @z );

SELECT @x, @y, @z;

END;

@x,@y,@z
1,(NULL),2

Short Answer: Don't use OUT, use INOUT instead, if you want SQL Anywhere to detect missing arguments in your CALL statements.

Long Answer: SQL Anywhere allows varying-length argument lists in CALL statements, and it assumes the omitted argument(s) correspond to parameters at the end of the list.

In other words, SQL Anywhere doesn't know @pb is the missing parameter, it thinks @pz is missing. And, apparently, the statement in the docs "If no default is set, and an argument is not provided, an error is given." doesn't apply to missing OUT arguments.

It should, of course

It makes no sense for an OUT argument to be optional since by definition the procedure is going to assign a value to the corresponding parameter, and there's no place for that value to go if the argument is missing.

Nonetheless, no error is displayed when "SET @pz = @pb + 99;" assigns a value to a parameter with no corresponding argument.

Workaround 1: Use INOUT instead of OUT

As suggested earlier, INOUT is a perfectly good substitute for OUT, no further changes to your code are required: You don't actually have to assign a value before calling the procedure since presumably the code inside the procedure is still going to treat it like an "out" parameter.

Now, if you add a parameter @p to the CREATE PROCEDURE and forget to change the CALL, you'll be told about it right away:

CREATE PROCEDURE p (
   IN    @px      INTEGER,
   IN    @pb      INTEGER,
   INOUT @py      INTEGER,
   INOUT @pz      INTEGER )
BEGIN
   SET @py = @px + 1;
   SET @pz = @pb + 99;
END;

BEGIN
DECLARE @x      INTEGER;
DECLARE @y      INTEGER;
DECLARE @z      INTEGER;

SET @x = 1;

CALL p ( @x, @y, @z );

SELECT @x, @y, @z;

END;

Could not execute statement.
Wrong number of parameters to function 'p'
SQLCODE=-154, ODBC 3 State="42000"

Workaround 2: Put OUT before IN

Another workaround is to code your procedures with all the IN and INOUT parameters after the OUTs. That way, if an argument is missing from the call, SQL Anywhere will think it's a missing IN or INOUT parameter and raise an error:

CREATE PROCEDURE p (
   OUT @py      INTEGER,
   OUT @pz      INTEGER,
   IN  @px      INTEGER,
   IN  @pb      INTEGER )
BEGIN
   SET @py = @px + 1;
   SET @pz = @pb + 99;
END;

BEGIN
DECLARE @x      INTEGER;
DECLARE @y      INTEGER;
DECLARE @z      INTEGER;

SET @x = 1;

CALL p ( @y, @z, @x );

SELECT @x, @y, @z;

END;

Could not execute statement.
Wrong number of parameters to function 'p'
SQLCODE=-154, ODBC 3 State="42000"

Personally, I like Workaround 1 better (use INOUT instead of OUT) because I like coding CALL statements with all the inputs first followed by the outputs.

Better yet, I'd like a product enhancement to require arguments be provided for all OUT parameters.

---