> SPs are precompiled and Views are prepared each time you use them.
> I use views when I know that a certain query will be used multiple
> times in my application and returns a subset of the data.

Thank you for the clarification. This is new information for me.

> Views can
> also be used to limit access rights to tables by assigning the user
> rights to the view instead of the table.

Yes, I read about this in an article by Ann Harrison on the ibphoenix
site recently. Very interesting, but i haven't tried implementing
this in a production database. I've been doing some reading on Database
Debunkings site (by Date, Pascal, et al)... wonders whether this might
be
a solution for complete 'data independence'...:)

> I use SPs for more complex
> operations, i.e. statistical data compiled from the financial data
> from the whole database or to concatenate more than one varchar field
> together, so one null field would return a null if concatenated with a
> non-null field.

OK.
To be more specific, I needed to provide a server-side construct to
calculate some data (summary aggregates) for a table. The operation
is fairly simple, (can be done as a view or an sp), but I'm concerned
about performance issues when the db got bigger.

I had several options when I started :
1. Computed Field.
2. SP (returns single value, CALC_ONE in my previous post)
3. SP (returns a set, CALC_TWO in my previous post)
4. VIEW

#1 is a bit less attractive since I got the impression that the
calculation will be done every time someone access the table.
#2 and #3 is equally feasible, but the possibility of slow joins
from the results are bothering me. As you can see, sooner or
later I would need to join the result set from the SP to the
original table. Putting every column that I need into the SP
seems to be the 'wrong' thing to do.
#4 is my personal favorite, before I ran into the knowledgeBase
article that says that I should use SP instead...:)

What I need

> SPs use the table indices for the DML operations within the SP.
> But, the SP doesn't have an index on its own result set.

So I suppose doing any join operations with the result set of an
SP can be expected to be slow....

> ...
> create procedure calc_one( record_id integer )
> returns ( somevalue numeric(18,4) )
> as begin
> ...
> end;
> ...
> as opposed to
> ...
> create procedure calc_two
> returns ( record_id integer, somevalue numeric(18,4) )
> as begin
> ...
> end;
> ...
> I use CALC_TWO style when the only data I want is returned by the SP.
> And, I use CALC_ONE style when the result set would require more
> field from mytable. If you would want to do the same with CALC_TWO,
> you'll need to include a JOIN in your query.

One interesting aspect (at least for me...:) that I encountered is that
with the CALC_ONE style, I expected to write something like :
...
select mt.record_id, calc_one(mt.record_id)
from myTable mt;
...
which is very intuitive, but would not work....:)

Thank you all for taking the time to answer my questions.

Salam,
sugi.