Stored procedures are great for many reasons but one of the reasons I might not use them is you can’t cache them…or can you?
A discussion at work enlightened me to a simple way around the lack of the cachedwithin attribute inside the <cfstoredproc> tag. Simply use the <cfquery> tag instead!
Ok, so normally if I wanted to use a stored procedure I might do something like:
<cfstoredproc procedure="qGetUser" datasource="#application.dsn#"> <cfprocparam cfsqltype="CF_SQL_INTEGER" null="false" type="in" value="#arguments.userId#" /> </cfstoredproc>
But did you know that you can execute your stored procedures from within the <cfquery> tag? Well you can, that way you can also take advantage of the cachedwithin attribute!:
<cfquery name="qGetUser" datasource="#application.dsn#" cachedwithin="#createTimeSpan(0,2,0,0)#"> exec usp_getUser #arguments.userId# </cfquery>
One thing though is that this will only work when you return a single record set, if you need to return multiple record sets (which I often do) then <cfstoredproc> is your only option as you can use the <cfprocresult> tag like:
<cfprocresult name="myResultOne" resultset="2" />
It always amazes me when you get simple solutions to problems you have using what you already know in a creative way, at least creative or new to you!
Always look to refactor your code, you never know what you’ll learn to save you time and effort in the future

5 Comments
I tried this technique for caching stored procedures. It just about halved the time of pages on our web site. Thanks for this! There was one big SP that was slowing the whole boat down. Caching it was key!
Excellent news Nate! Glad it helped. I think too many people underestimate the power of caching…all kinds of caching.
Hi, does anyone can explain qGetUser and usp_getUser in the cfquery tag, what they are stand for, thx in advance.
@Michael – qGetUser is the ColdFusion variable name of the query so you can use it in your cfm templates to loop over query results etc.
usp_getUser would be a user defined stored procedure on the database server which ColdFusion is executing using cfquery instead of the normal cfstoredproc tag.
Bit of an update, since this is a really cool topic. Adobe indicates that as of CF8 the cfstoredproc tag supports cachedWithin(), same as cfquery. http://www.coldfusionjedi.com/index.cfm/2008/4/25/Ask-a-Jedi-Caching-Stored-Procedures