Hi Sergey,

> Sorry for the long example/question, I just coudn't make it
> shorter :)
>
> Let's have a table T1 with one field, DATELP : DATE;
> and fill it with two values, '1.5.2002' and '1.6.2002',
> for example.
>
> Let's make procedure P1, with
> DATEINPUT : DATE as input parameter, and
> DATEOUTPUT : DATE as output parameter, and body:
>
> BEGIN
> SELECT FIRST 1 DATELP FROM T1
> WHERE (DATELP > :DATEINPUT)
> ORDER BY DATELP
> INTO :DATEOUTPUT;
> SUSPEND;
> END
>
> If we execute procedure with DATEINPUT = '1.1.2004',
> output result is NULL, as expected, because there isn't
> a date bigger than '1.1.2004' in the table.

It's NULL because variables and (output) parameters are
initialized with NULL.

> So, let's change the procedure, just add one statement
> into body:
>
> BEGIN
> DATEOUTPUT = '1.1.2000'; /* <-- */
> SELECT FIRST 1 DATELP FROM T1
> WHERE (DATELP > :DATEINPUT)
> ORDER BY DATELP
> INTO :DATEOUTPUT;
> SUSPEND;
> END
>
> Now, if we execute the procedure with the same input value,
> it returns '1.1.2000' instead NULL.
> It seems, that select (or INTO clause) do not force
> value into parameter (dateoutput), if the value is null.

Nope - the output result is alright. There IS NO RESULTSET -
so there's nothing to put into "dateoutput", therefore, the previous
value stays in the variable/parameter.

> And, 3-rd example:
> Procedure P3, with the same i/o parameters, as above,
> and with body:
>
> BEGIN
> DATEOUTPUT = '1.1.2000';
> SELECT DATEOUTPUT FROM P1 ( :DATEINPUT )
> INTO :DATEOUTPUT;
> SUSPEND;
> END
>
> If we "hide" select into procedure (P1), returned value
> is again NULL, as it has to be.

P1 with the "dateoutput = '1.1.2000'" or without it?


With regards,

Martijn Tonies
Database Workbench - developer tool for InterBase, Firebird & MS SQL Server.
Upscene Productions
http://www.upscene.com