Subject Re: [firebird-support] Value from parmeter-dependant colum in where clause
Author Martijn Tonies
> >> I want my select statement to return a value from a different column
> >>depending on one of the in-parameters (to a stored procedure).
> >>If I simplify the problem I have two different price columns and which
> >>to use is dependant on one of parameters to the SP.
> >For
> > select name,
> > priceA,
> > priceB
> > end as Price
> > where Price > 10
> > from myTable
> > into name_out, :v_PriceA, :v_PriceB
> > do begin
> > if (type = 1)
> > then price_out = v_PriceA;
> >else price_out = v_PriceB;
> <suspend;
> >end
> Thanks a lot! That is obvious now when I see it. Until now I have just
used stored procedures as beautified up select cases, not as real
procedures, I think I just climbed a small step on my SP knowledge ladder.
> In general, is the use of code in stored procedures expensive in terms of
speed? I think the question really is, is it advisable to put as much logic
as possible into large select statements or is the beauty of smaller select
cases combined with code to be preferred?

That depends ... IMO, joining against a procedure with a SUSPEND statement
less wise when it comes to performance. However, if the procedure contains
like the above, it might be hard to do it in a different manner.

Some use procedures ALWAYS for general SELECTs, eg, to load a customer
(which can be anything combined in many tables):

select ... all sorts of columns ...
from customer_proc(:optionalID?)

Now, this query would be the same in all applications and therefor, the
internals can
change without affecting running applications.

With regards,

Martijn Tonies
Database Workbench - developer tool for InterBase, Firebird, MySQL & MS SQL
Upscene Productions