Skip to main content
D
Dan_Bracuk
Inspiring
April 13, 2010
Question

Calling Stored Procedures

  • April 13, 2010
  • 2 replies
  • 1325 views

I'm curious as to whether either of these methods has any advantages over the other.  They both work.

Method 1

<cfstoredproc datasource="infograms" procedure="DanTest">
<cfprocparam cfsqltype="cf_sql_timestamp"  value="#startdate#">
<cfprocresult name="y">
</cfstoredproc>

Method 2

<cfquery name="z" datasource="infograms">
execute DanTest @7246612 = <cfqueryparam cfsqltype="cf_sql_timestamp" value="#StartDate#">
</cfquery>

This topic has been closed for replies.

2 replies

A
Adam Cameron.
Inspiring
April 14, 2010

Well you're gonna be out of luck with using <cfquery> if your proc returns more than one recordset.  That's probably the major consideration.

Also procs don't necessarily return recordsets, however that's all a <cfquery> can return.

In addition to this, it might be worth reading up on how JDBC passes proc calls as opposed to SQL strings too... there's likely to be some considerations there too.

My primary thought here is... if there's a specific mechanism to call a proc - ie: <cfstoredproc> - why would one use something not designed for the job, ie: <cfquery>.  So turn the question around.  Why would one use <cfquery> to do this when one can use <cfstoredproc> ?

--
Adam

P
paross1
Participating Frequently
April 14, 2010

That is a good question. Have you run them with debugging enabled and is there a significant difference between them in terms of execution time, etc.?

D
Dan_BracukAuthor
Inspiring
April 15, 2010

I figured a slow sp would be a good candidate.   This:

<cfloop from="1" to="3" index="i" step="1">
<cfstoredproc datasource="something" procedure="a_slow_sp" result="x">
<cfprocparam dbvarname="@StartDate" cfsqltype="cf_sql_timestamp" type="in" value="#StartDate#">
<cfprocparam type="in" dbvarname="@EndDate" cfsqltype="cf_sql_timestamp" value="#EndDate#">
<cfprocresult name="results">
</cfstoredproc>

<cfdump var="cfstoredproc #x.executiontime#">
<br />

<cfquery name="y" datasource="something" result="yy" timeout="100000">
exec a_slow_sp @StartDate = <cfqueryparam cfsqltype="cf_sql_timestamp" value="#StartDate#">
, @EndDate = <cfqueryparam cfsqltype="cf_sql_timestamp" value="#EndDate#">
</cfquery>

<cfdump var="cfquery #yy.executiontime#">
<br />
</cfloop>

Results in this:

cfstoredproc 78439
cfquery 76471
cfstoredproc 75392
cfquery 78768
cfstoredproc 76767
cfquery 76518

A
Adam Cameron.
Inspiring
April 15, 2010

So not much in it, timewise.

Note: dbvarname is an obsolete attribute: it does not work.

--

Adam

Terms & ConditionsAccessibility statement
Using the community Terms of use Privacy Cookie preferences Do not sell or share my personal information ad choicesAdChoices