Forum OpenACS Development: Re: script that moves sql from tcl to xql files
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.