If you have legacy code, you likely have lots (if not all) of your SQL in CFQUERY tags. Perhaps you have wondered if (or been told) you should convert all that SQL into stored procedures (called via CFTOREDPROC). There are certainly pros and cons to considering the task–and indeed a LOT of reasons why it’s a GOOD idea to consider it.

But would you be interested to hear a counterpoint?

Here’s a discussion on the topic from SQL Server guru Brent Ozar, who discusses some pragmatic reasons when it may make sense to NOT make the conversion, especially in consideration of who is better off doing the coding, whether developers or DBAs, depending on your team’s makeup:

https://www.brentozar.com/archive/2019/03/should-we-use-stored-procedures-or-queries-built-in-the-app/

He also offers a few scenarios to help you assess when each approach may make more sense.

More considerations

And there are many useful comments which follow the post, which are worth considering also (including a mention of ORM, for those who may propose that as a different answer). You can offer replies there or here as seems appropriate.  Of course he is not thinking of CF there, but what he says applies to SQL generated from CF as much as from ASP.NET, PHP, and so on–and as much for those using SQL Server as using other databases.

I’ll note also that Brent doesn’t discuss the importance of using parameterized queries (CFQUERYPARAM, in the case of CFML) to help with preventing sql injection, but that’s because he’d assume his audience understood the value of that (and he’s discussed it in other posts in the past).

As I said at the open, there are certainly lots of GOOD reasons one SHOULD convert to using stored procedures. Here’s a different blogger who presents the classic sort of arguments in favor of it here: https://codingsight.com/dynamic-sql-vs-stored-procedure/.  And FWIW, if you may go seeking still more resources to help with the consideration, note that they this blogger uses the term “dynamic sql”, referring to such SQL generated in a client app (like CF would be), and that is indeed the point of Brent’s post as well. You will also find it referred to as “ad hoc” sql.

To close, I don’t offer the post as click-bait or to spark controversy. I saw it in a retrospective he did today of his most popular posts of the year, and I thought some here may be interested to consider the thoughts since it’s indeed generic enough to apply to CFML and indeed any database.

Indeed, I wish everyone a happy new year, and here’s to CF2020 coming down the pike.

For more blog content from Charlie Arehart, see his posts here as well as his posts at carehart.org. And follow him on Twitter and other social media as carehart.