Forum OpenACS Development: Re: script that moves sql from tcl to xql files
I do recall one situation where my code only worked when I moved the SQL to the XQL file (normally like you I find it much easier to leave the SQL in the TCL file during development) - I can't find it right now but I think this was to do with using pagination.
Another advantage to using XQL files is that you can reuse a query (using db_qd_replace_sql https://openacs.org/api-doc/proc-view?proc=%3a%3adb_qd_replace_sql%20&source_p=1 but it's not always as straightforward as that!). Although in practice, if a query merits being reused, it probably also merits being put in a library TCL proc.
A nasty problem might be the following: when the query specified both in the .tcl and .xql file, then the query in xql has higher precedence. Therefore it could be completely misleading to read and maybe modify the query in the tcl code (changes have no effect), but modifying the query in xql "works".
The actual reuse of sql queries in different procs is very little, at least in the most common 80 packages. i observed the rather opposite that the same sql query is repeated in multiple .xql files: i remember this since i had to fix the same problems multiple times.
There were some esoteric seeming cases while working with the ecommerce package that seemed to break as you suggest, but the problems ended up being some thing else. For example some were due to having more than one query using the same reference, or a cache'd query using an early coded caching technique before OpenACS had a fully standardized one. A particularly difficult case involved a poorly nested curly brace in a long tcl file that defined scheduled procs; Another involved an xql definition in a www dir; In both cases, I didn't realize that code there are only re-interpreted on server restart.
I agree, avoid duplication by making a proc out of it. Also avoiding functions and procs in sql help keep the system with a consistent interpretation of data and timing.