--- In firebird-support@yahoogroups.com, Helen Borrie <helebor@t...>
wrote:

> At 11:42 AM 20/08/2005 +0000, you wrote:
> >I'm developing a commercial application that uses Firebird. At the
> >moment I have all my queries coded in stored procedures.
>
> This is extremely unnecessary.
>
>
> >The process to deploy updates to my app I think is going to be
> >painful, because for example, in order to modify a field I have to
> >delete every stored procedure that reference that field, make the
> >modifications and recreate the stored procedures.
>
> Exactly.
>
>
> >I'm thinking of dropping all my stored procedures and use dynamic sql
> >directly from my code instead.
> >
> >I would like to know your opinion about this. What drawbacks can I
> >expected?
>
> My opinion is that the sooner you replace any selectable SPs that do
> nothing except encapsulate regular SELECT queries in permafrost, the
> better. Keep the ones that provide workarounds for retrieval logic

that

> SQL doesn't support, or supports badly; ditch the rest.
>
> The same goes for executable SPs that perform plain-vanilla DML.

They are

> a waste of resources and they add dependencies that will drive you

crazy

> with their sheer volume. Use executable procedures to perform

sequences of

> operations that can be kicked off with one set of parameters

(replaceable

> or not) from the client.
>
> The biggest drawback with dynamic queries is the developer.
>
> To be more specific, you get a lot of developers who have only used

desktop

> databases before and try to make Firebird work like Access or, worse, a
> spreadsheet application. They don't understand parameterised

statements,

> they don't understand transaction isolation or atomic tasking or

delegation

> of data-intensive work to the server. And, no kidding, sometimes

they don't

> even understand SQL and have been observed to argue that they don't

want to

> be put into the position of having to learn it.
>
> Use parameters for your dynamic SQL as much as you can. That means
> everything except user-specified queries that are completely ad hoc by
> design. Most user inquiries are not ad hoc. They want the same query,
> over and over, with different values in the search (WHERE)
> criteria. "Prepare once, execute often."
>
> Well, you did ask for opinions... :-)
>
> ./heLen

Thank you very much for your advice. I really apreciate it.

Jose.