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
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 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
<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 :)