Re: help with select into

Laura Bellini wrote:

> Hi.
> I'm trying to do a fairly simple select into within a stored procedure, but
> I'm having difficulty getting the statement or statement thereafter to
> handle the situation when no data is returned.
>
> Select match_prod_id
> into new_prod_id_out
> from product_up_cross_sell
> where product_up-cross_sell.base_prod_id = prod_id_in
> and product_up_cross_sell.match_type = 'U'
>
> Prod_id_in is an incoming parameter.
> If there is no row returned, this is fine - new_prod_id_out will be
> 'empty'.
> What I want, however, is to populate new_prod_id_out with another
> variable's value if this select statement returns no rows.
>
> I DO NOT want the procedure to hit an exception and end. All I want to do
> is fill in this variable with a value, and continue.

You will hit an exception if your select returns more than one value, though I'm not
sure what will happen if no rows are returned. Is new_prod_id_out null when no
rows are returned? From the sound of it, you're hitting an exception.

The following will work but it's a hack: before you populate new_prod_id_out with
your SELECT statement, SELECT COUNT(*) into nVar using the same where clause. You can then check for nVar=1 which means you can safely execute your select into. If nVar = 0,
you can populate new_prod_id_out with the other variable's value. If nVar > 1, then there's
a logic error.

Hope this helps
jc Received on Fri Jan 16 1998 - 00:00:00 CST