on 2024 May 13 9:54 AM
I have a stored procedure like this:
create procedure "dba"."xxx"( in @param1 integer,in @param2 integer,in @param3 varchar(4096) default '' ) begin if @param3 = '' then /* ... */ else /* ... */ end if end
Since a long time this worked well as expected. Actually a customer reported a problem and I noticed by testing the following call:
call dba.xxx(11,1)
@param3 comes with value '0'. I am really suprised. Under which circumstances is the default value not reliable?
Request clarification before answering.
With 17.0.11.7672, default parameters work as expected, and I have never had issued with those in previous versions.
Even an identially named connection variable does not interfere with parameters, and the results are as expected:
create or replace variable @param3 varchar(4096) = 'Dummy variable might get in the way'; create or replace procedure "dba"."xxx"( in @param1 integer,in @param2 integer,in @param3 varchar(4096) default '' ) begin if @param3 = '' then select '@param3 is empty string'; else select '@param3: "' || @param3 || '".'; end if; end; select @param3; -- returns 'Dummy variable might get in the way' call dba.xxx(1, 2); -- returns '@param3 is empty string' call dba.xxx(1, 2, 'What?'); -- returns '@param3: "What?".' call dba.xxx(1, 2); -- returns '@param3 is empty string' call dba.xxx(1, 2, default); -- returns '@param3 is empty string' call dba.xxx(1, 2, null); -- returns '@param3: "".'
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
Are you able to collect a request level log ( -zr all -zo requests.rll or via sa_server_option). I would be interested in seeing the difference in how calls from NativeDB are being made. It has been some time, but I seem to recall that one of the "3rd party" connection options for Delphi/C++ are embedded SQL based.
Did you or your customer connect via TDS? If so, I believe '' is considered equivalent to NULL and comparing anything against NULL would be false (actually 'unknown')?
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
User | Count |
---|---|
76 | |
30 | |
8 | |
8 | |
7 | |
7 | |
6 | |
5 | |
4 | |
4 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.