chapter31

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

Related Pages

• cfstoredproc vs cfquery
• new ‘result’ attribute in MX7 cfstoredproc
• Dumping and aborting with <cfscript>

This entry was posted on Wednesday, November 8th, 2006 at 1:29 am and is filed under ColdFusion. You can follow any responses to this entry through the RSS 2.0 feed. You can leave a response, or trackback from your own site.