Design advice

I’m developing a commercial application that uses Firebird. At the moment I have all my queries coded in stored procedures.

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.

I'm thinking of dropping all my stored procedures and use dynamic sql directly from my code.

I would like to know your opinion about this. What drawbacks can I expected?

I am working on an accounting package and from my point of view you should use both. while updating database it is good to use direct sql statment from program, but some complex quries could not be performed easily in program. SPs are quite helpful for me when i need formatted data for reports, and in case of big queries itis very handy cause it minimize the time of result which is critical in big programs , as it is in my case.
So use SQL and SP side by side according to your requirement.

Jusm make sure that ALL queries will be faster if prepared once and executed many times.
I mean, try to make every query parametarized, so you can pass only param values to them. I know that this is not allways possible...

--
Best regards,
Fikret Hasovic http://fikret.fbtalk.net
USAID TAMP Senior Programmer

* Firebird Foundation Committee member.
- Join today at http://www.firebirdsql.org/ff/foundation
* JEDI VCS contributor
http://jedivcs.sourceforge.net/
* Firebird and Fyracle news
http://www.fyracle.org

You could also try to comment the procedure(s) like this:
/* PROCEDURE TEXT
PROCEDURE TEXT */
(creating a routine that comments a given procedure would probably be a piece of cake)
then modify what you need ... then uncomment it

Simple matter - use select+cached updates for documents and stored procedures for select data for complex reports, why choose any other way?