|Subject||Re: [IBO] stored procedures versus queries|
> should i, if i am responsible for developing both the serverHi Jon,
> database and the user interface, used stored procedures to select
> data for viewing, update data, and otherwise control all the
> transmission of data from the server to the client, and call them
> thru stored procedure objects instead of query objects?
Different people will have different answers to this question.
The advantage of stored procedures is that they help isolate the user
interface from the underlying data structures, plus you can do lots of
interesting things inside them, simulate fields and records etc etc.
The use of stored procedures inside TIB_Query can provide a great way
of controlling (from the server) which fields can be considered
editable etc - without needing to configure readonly settings in the
You can achieve *some* of the same effects using views - but they have
their own complications.
The downside of stored procedures include...
More difficult to setup - need separate procedures for insert, edit
etc. You can use stored procedures inside TIB_Query, you dont have
to use multiple TIB_StoredProc components.
Performance difficulties when you need to refresh a specific record
(you cant, you have to re-read the entire select procedure).
Performance issues when you want to change order of selection. The
procedure will still operate the way it always does - so the entire
set must be extracted and sorted externally. When you use tables
and views IB/FB can identify indexes that may make a different sort
order work effectively.
Not portable - select procedures are not available in all SQL
You cant define parameters and return values using domains. This
means you cannot use some of the features in IBO relating to
domains (that can save on coding and configuration time).
My own preferences vary from project to project. In large projects I
generally query directly against tables or views - as the performance
issues become very important. I then only use stored procedures when
a direct query cannot give me the results that I want. In small but
complex projects I often revert to stored procedures to get the level
of control that I prefer.