Please be aware that the SAP SQL Anywhere Forum will be shut down on August 29th, 2024 when all it's content will be migrated to the SAP Community.

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

IN @parm1 integer,
IN @parm2 integer DEFAULT NULL )

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

--- 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.

asked 29 Jan '10, 19:35

Ron%20Hiner's gravatar image

Ron Hiner
accept rate: 9%

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.

permanent link

answered 29 Jan '10, 20:48

Glenn%20Paulley's gravatar image

Glenn Paulley
accept rate: 43%

Your answer
toggle preview

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here



Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text]( "title")
  • image?![alt text](/path/img.jpg "title")
  • numbered list: 1. Foo 2. Bar
  • to add a line break simply add two spaces to where you would like the new line to be.
  • basic HTML tags are also supported

Question tags:


question asked: 29 Jan '10, 19:35

question was seen: 2,977 times

last updated: 29 Jan '10, 20:48