5 Replies Latest reply: Apr 15, 2010 7:18 AM by Dan Bracuk RSS

    Calling Stored Procedures

    Dan Bracuk Community Member
    Dan Bracuk Apr 13, 2010 9:55 AM

      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 @p1 = <cfqueryparam cfsqltype="cf_sql_timestamp" value="#StartDate#">
      </cfquery>

      • 881 Views
      • Tags:
        • 1. Re: Calling Stored Procedures
          paross1 Community Member
          paross1 Apr 14, 2010 6:43 AM (in response to Dan Bracuk)

          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.?

          • 2. Re: Calling Stored Procedures
            Adam Cameron. Community Member
            Adam Cameron. Apr 14, 2010 7:05 AM (in response to Dan Bracuk)

            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

            • 3. Re: Calling Stored Procedures
              Dan Bracuk Community Member
              Dan Bracuk Apr 15, 2010 5:43 AM (in response to paross1)

              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

              • 4. Re: Calling Stored Procedures
                Adam Cameron. Community Member
                Adam Cameron. Apr 15, 2010 6:13 AM (in response to Dan Bracuk)

                So not much in it, timewise.

                 

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

                 

                --

                Adam

                • 5. Re: Calling Stored Procedures
                  Dan Bracuk Community Member
                  Dan Bracuk Apr 15, 2010 7:18 AM (in response to Adam Cameron.)

                  I think I prefer the cfquery approach for two reasons.  First, only one tag is required.  With cfstoredprocedure you need at least two.  Second, I like to see that coldfusion variable x matches up with cp input paramter y.  It helps me understand what I am doing.