Subject [firebird-support] Re: Using stored procedure results in the WHERE clause
Author Svein Erling Tysvær
>First, thanks for your suggestions.
>If I use a COMPUTED column, any time I use SELECT * FROM OPS_STOP_REC that column is going to be evaluated.
>This would end up doing a lot of I/O in those cases where I don't care about the value of the computed column.
>Since I use SELECT * FROM OPS_STOP_REC quite a bit in other places I don't really see this as a viable option.
>Yes, I could just select those columns that I care about, but the list is quite long and it becomes a
>maintenance nightmare as new columns are added.
>I could define a view except that I have several of these conditions that may be used in different combinations.
>In which case I would need a view for each possible combination.
>Got to think about this some more.

I'd say a view is always an alternative to a computed column! Now, I don't know whether this statement is true or not, it is just that from the top of my head I cannot think of any situation where it is false. Having lots of SELECT * FROM OPS_STOP_REC can make adding computed columns undesirable. Choosing to use

SELECT *, <computedfield1>, <computedfield2>...

would mean that you simply could use V_OPS_STOP_REC rather than OPS_STOP_REC whenever a calculated field was important. Moreover, I don't quite follow when you say that you need a view for each possible combination, rather, I'd say you need a new column for each possible combination and that whether this additional field should be added to an existing view or a new view is something to investigate for each situation.

Note that I'm not saying that a stored procedure always could be replaced by a view, there are definitely situations that call for stored procedures. I'm just saying that computed columns can be replaced by views. Although possible with a view, I think I would prefer a computed column if its content can be determined entirely from the content of the current row, whereas I would prefer a view if other tables or rows were involved in the computation.

Myself, I too rarely use computed columns, views or stored procedures since my queries typically vary quite a bit and I find that CTEs normally fit my need for intermediate calculations. More rarely, I find that EXECUTE BLOCK is the best way to get the result set I want.