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

asked 29 Jan '10, 19:35

Ron%20Hiner's gravatar image

Ron Hiner
880202427
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
10.7k571104
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

By RSS:

Answers

Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text](http://url.com/ "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:

×106
×19

question asked: 29 Jan '10, 19:35

question was seen: 1,264 times

last updated: 29 Jan '10, 20:48