Setting NULLs for all output parameters of SP in one line

[Tomasz Kujałowicz]

Hello,

 I have many complicated SPs in my database, which returns multiple combinations of data in output parameters.

    I have to frequently clear output parameters (set nulls).

    Is there in Firebird any internal function or trick to set nulls for all output parameters of stored procedure?

    For example I have procedure:

CREATE OR ALTER procedure tmp_bbb
returns (
    a_out integer,
    b_out integer,
    c_out integer,
    d_out integer,
    e_out integer,
    f_out integer)
as
begin
  a_out=1;
  b_out=2;
  c_out=3;
  d_out=4;
  e_out=5;

  suspend;

  f_out=6;

  a_out=null;   --How to replece this nulls with one function?
  b_out=null;
  c_out=null;
  d_out=null;
  e_out=null;

  suspend;

Maybe "suspend" should has parameter which forces set nulls for all output parameters?

[Mark Rotteveel]

On 22-03-2021 14:00, Tomasz Kujałowicz wrote:
>  I have many complicated SPs in my database, which returns multiple
> combinations of data in output parameters.
>
>     I have to frequently clear output parameters (set nulls).
>
>     Is there in Firebird any internal function or trick to set nulls
> for all output parameters of stored procedure?

No there is no such function. You need to try writing your stored
procedures in a way that they will naturally assign null or the desired
value to all columns for the next fetch, and not rely on having to
explicitly null the output columns for each fetch.

[..]

>
> Maybe "suspend" should has parameter which forces set nulls for all
> output parameters?

No it should definitely not do that. The preservation of values after a
SUSPEND is a very useful feature, consider the use of nested loops where
the outer loop populates two output columns, while the nested loop(s)
populate the rest of the output columns. In your proposal you would need
to have additional local variables to preserve the value, and have the
additional burden of having to reassign those values to the output
columns after each SUSPEND.

Mark
--
Mark Rotteveel

[Svein Erling Tysvær]

I suppose you could do something like

for select null, null, null, null, null

from rdb$database -- This table always contains one row

into :a_out, :b_out, :c_out, :d_out, :e_out do ;

though if it is only five fields, it could be simpler to just set each of them individually (as you did in your example).

Set

--
You received this message because you are subscribed to the Google Groups "firebird-support" group.
To unsubscribe from this group and stop receiving emails from it, send an email to firebird-suppo...@googlegroups.com.
To view this discussion on the web, visit https://groups.google.com/d/msgid/firebird-support/516b86af-fe76-7701-19d7-821226b4e6f6%40lawinegevaar.nl.

[Tomasz Kujałowicz]

W dniu 2021-03-22 o 14:06, Mark Rotteveel pisze:

On 22-03-2021 14:00, Tomasz Kujałowicz wrote:

  I have many complicated SPs in my database, which returns multiple combinations of data in output parameters.

     I have to frequently clear output parameters (set nulls).

     Is there in Firebird any internal function or trick to set nulls for all output parameters of stored procedure?

No there is no such function. You need to try writing your stored procedures in a way that they will naturally assign null or the desired value to all columns for the next fetch, and not rely on having to explicitly null the output columns for each fetch.

I make it now, but this generate a lot of null assiging. The code is unclear.

For exampe, I have some sections in store procedure. The first section contains complicated joining multiple tables and returns multiple (100+) columns. Next section returns for example 2 columns with nulls others. And this situation repeate multiple times (this 2 sections with different joinings). Returning unused columns with data has not sense. So we have nulls returns columns multiple times, which makes code unclear and difficult for repair in the future. Such "rely on having to explicitly null" is ideal in our situation.

[..]

Maybe "suspend" should has parameter which forces set nulls for all output parameters?

No it should definitely not do that. The preservation of values after a SUSPEND is a very useful feature,

I know and I agree. In many situations it preserves of using multiple local variables.

But in other situations it require multiple null settings.

So such suspend with additional parameter (for example suspend(true) ) will be perfect. Or any function to use in stored procedures which set nulls for all output parameters could be good (for example nulls_out_params() ).

consider the use of nested loops where the outer loop populates two output columns, while the nested loop(s) populate the rest of the output columns. In your proposal you would need to have additional local variables to preserve the value, and have the additional burden of having to reassign those values to the output columns after each SUSPEND.

I think In some situations such simple nulls setting will finally have better performance than using additional loops or other strategies . The worse is returning unused values to client .

Mark

Thank you Mark for response.

Please consider such functionality. Maybe it helps others.

-- 
Tomasz Kujałowicz
tel.: +48 509 155 996
e-mail: tomasz.k...@gmail.com