Hi Helen,

Helen Borrie wrote:

>>What is the general rule?
>
> Use SELECT for a SP that you have defined to return a dataset. This style
> of SP is distinguished by a FOR...SELECT....DO...SUSPEND loop, each
> iteration of which outputs a single row of a dataset.

ok, i use TIB_Cursor or TIB_Query for that.

> Use EXECUTE for a SP that performs *anything* except a FOR..SELECT..etc.

yep, TIB_DSQL is my tool of choice here...

> Don't design SELECTable SPs that modify data (yes, the engine will let you
> do it but commonsense and ACID rules say "No").

errr... at the risk of outing myself as a nonconformist (which i am :-) ...

what is so bad about SELECTable SPs that modify data? i use them a lot and i
really like them - honestly, to me they're about the best part of SPs! (what you
can't do in Delphi: functions with <n> input and <m> output params)

i ususally use them instead of pure DML SPs, so they return the data they
modified. this way you can test the SP with a SELECT * FROM SP (in a TIB_Cursor
or a TIB_Query with a grid attached), check if what it does is alright, and just
rollback the trx if anything's wrong. The only downside i can see is that you
must remember to fetch all result rows until EOF before the COMMIT or some DML
will be left out...

In the SPs i have SUSPENDs in the FOR..SELECT loop(s) and an additional EXIT
right at the end - which is not necessary but won't do any harm either. it's
more out of habit because you need it in the pure DML SPs anyway, so *all* my
SPs have an EXIT before the last END... all i can say is that this setup works
fine for me.

so i am really curious why you recommend against those SELECTable DML beasts??
(Warning: if you can give good reasons i'll likely have to rewrite half of my
app - which is running in production for three years now ;-)

regards & tia,
fingerman

P.S.: i know what commonsense is (not that i'm sure i have it, but i know what
it is<g>), and i know acid jazz, but what on earth are "ACID rules" ???

--
-------------------------------------------------------------------------
when parsers parse, and compilers compile, then why don't objects object?

fingerbirdy - fingerman's door to Firebird
http://www.fingerbird.de