Stored procedures are bad, m'kay?
It really depends on your situation, in terms of stored procedures vs no stored procedures.
1. Is this for an intranet, extranet or internet site?
2. How many different records are you planning to pump thru the procedure?
3. How many different logic flows, looping would you need?
I do agree that stored procedures aren't the solution for everything, and perhaps there was this drive towards stored procedures as a solution for bad coldfusion/sql programming, to help produce great results in reasonable performance.
It just depends on your needs.
If it's a 1-time query, then you don't need to save it as a stored procedure, unless for example, it's for a monthly/yearly report, in that case you would save it, so that you won't have to redo it, the next time you need it.
The real issue is whether you pay attention to how your queries affect both the load on coldfusion and sql server, but how it affects the load time of your visitors/users.
Sidenote: There seems to be two major opinionated camps, those that think sticking to best practices and reasonable coding standards is a great idea, and those that think it's not that important or even a waste of time.
I know that I and a few other bloggers, really recycle those standards and best practices and standards. And I'll acknowledge discussing them isn't hip or cool, but it's really out of a desire to encourage everyone to perfect their craft.
Because when you release crappy code, that isn't documented, or crashes after it's taken from Access to SQL Server, or any number of the experiences I and thousands of coders have experienced in cleaning up after other people's work.
It's just a pain in the butt, it makes you look bad, and it makes us waste time trying to do the job you should have done...
Maybe we should start sharing some of our horror stories...