Query referencing practices question

[Crowley16]

basically, I am wondering if it's better:
1) to have a LOT of queries, and reference the queries everywhere, i.e. code, Control.Rowsource, report, form.recordsource
Or
2) to only save complicated queries and use inline sql where possible, and hard coded sql when in code.

the main advantages of 1 is it would be easier to edit/control/see/search actual saved queries so you won't have to trawl through all the controls on a form to see where the bad reference is and so on, also, for often used references, you only have to declare the query once. However this does mean there would literally be 100s of queries, and a LOT of them will be very similar, plus there'll probably be lots of redundant queries.

the advantages of 2 is it keeps the amount of saved queries small, and you can easily spot your big and important queries, plus when building, it's more intuitive to code the sql right there, plus it's clear what that sql is used for.

So, any opinions on this?

Thanks

--------------------
Procrastinate Now!

 

[RoyVidar]

Re #1
- also - there are some "precompilation" of stored queries, making them more effective than dynamic sql
- also - when merging/upsizing to other databases, where sql injection attacks are more dangerous/serious threaths - using either stored parameterized queries (stored procedures), or at least utilizing the parameters collection of the library one is using, greatly reduces that risk (see for instance faq709-1526)
- lot of queries, sure, but - you know where they are vs looking in every module/event, rowsource, recordsource ...

But - I don't think you'll get any "which is better" answers, probably only preferences ... ;-)

Roy-Vidar

 

[Crowley16]

hmm, sql injection, never considered that...

--------------------
Procrastinate Now!