Question

-- Not sure if this a bug, or a lack of understanding on my part... but I want to use a DEFAULT value of NULL on a stored procedure parm, and SQLA returns a value of 0 when I would expect a either NULL or a type mismatch error.
-- I'm using SQLA 9.0.2.3850

CREATE PROCEDURE parm_test (
IN @parm1 integer,
IN @parm2 integer DEFAULT NULL )

BEGIN
Message 'Parm 1 value is : ' || @parm1 to client;
if @parm2 = 0 then
Message 'Parm 2 value is zero, zip, nada.' to client;
ELSE
Message 'Parm 2 value is : ' || @parm2 to client;
end if;
END

--- Now... I call the procedure...

Call parm_test (1) -- it works as expected. Parameter 2 defaults to Null
Call parm_test (1,2) -- that works as expected.

-- Here is the problem:
Call parm_test (1, '') -- pass an empty string in as the second parm
-- The procedure doesnt error out on a data type mismatch. It accepts the input of an empty string and converts it not to a NULL, but to a value of 0.

-- Seems to me that the empty string on the call is most likely caused by a programmer's error, but SQLA converts it to what could be a valid value. I don't think that's right.

Answer 1

The procedure call is handling type conversion of its arguments precisely the same way in which the server performs type conversion during query execution. For example,

select cast( '' as integer )

also gives 0 as a result. Unlike other DBMS products, SQL Anywhere does differentiate between NULL and the empty string, and when coercing string values to numerics the server treats both '0' and the empty string as 0.