CFStoredProc vs CFQUERY

I was just wondering if any one knew the difference between the following: <CFSTOREDPROC PROCEDURE="usp_myproc" DATASOURCE="mydatasource">     <CFPROCPARAM TYPE="IN" CFSQLTYPE = CF_SQL_DATE VARIABLE = FOO dbVarName = @param2>     <CFPROCPARAM TYPE="IN" CFSQLTYPE = CF_SQL_INTERGER VARIABLE = BAR dbVarName = @param3> </CFSTOREDPROC> AND THIS: <CFQUERY Name="qryMyQuery" DATASOURCE="mydatasource">     {CALL usp_myproc("01/23/1971", "31")} </CFQUERY> Also, when the stored procedure returns a query, how would you access it through the first method?  The second method at least allows you to name the query so that you can access the resulting recordset. Thanks, Steve Durette Database Administrator/Web Developer Engineering/Construction System Support SBC/Ameritech ______________________________________________________________________ Why Share?   Dedicated Win 2000 Server · PIII 800 / 256 MB RAM / 40 GB HD / 20 GB MO/XFER   Instant Activation · $99/Month · Free Setup   http://www.pennyhost.com/redirect.cfm?adcode=coldfusionc

I was just wondering if any one knew the difference between the following: <CFSTOREDPROC PROCEDURE="usp_myproc" DATASOURCE="mydatasource">     <CFPROCPARAM TYPE="IN" CFSQLTYPE = CF_SQL_DATE VARIABLE = FOO dbVarName = @param2>     <CFPROCPARAM TYPE="IN" CFSQLTYPE = CF_SQL_INTERGER VARIABLE = BAR dbVarName = @param3> </CFSTOREDPROC> AND THIS: <CFQUERY Name="qryMyQuery" DATASOURCE="mydatasource">     {CALL usp_myproc("01/23/1971", "31")} </CFQUERY> Also, when the stored procedure returns a query, how would you access it through the first method?  The second method at least allows you to name the query so that you can access the resulting recordset. Thanks, Steve Durette Database Administrator/Web Developer Engineering/Construction System Support SBC/Ameritech ______________________________________________________________________ Dedicated Windows 2000 Server   PIII 800 / 256 MB RAM / 40 GB HD / 20 GB MO/XFER   Instant Activation · $99/Month · Free Setup   http://www.pennyhost.com/redirect.cfm?adcode=coldfusiona

To access results from a cfstoredproc you would use <cfstoredproc procedure="usp_myproc" datasource="dns">   <cfprocresult name = "query_name" resultSet = "n" maxRows = "maxrows">   <cfprocparam ..... </cfstoreproc> Whenever I use a stored procedure I call it with a cfquery. I'm not sure if there are any performance differences, but it's just easier to use a cfquery. ______________________ steve oliver cresco technologies, inc. http://www.crescotech.com I was just wondering if any one knew the difference between the following: <CFSTOREDPROC PROCEDURE="usp_myproc" DATASOURCE="mydatasource">     <CFPROCPARAM TYPE="IN" CFSQLTYPE = CF_SQL_DATE VARIABLE = FOO dbVarName = @param2>     <CFPROCPARAM TYPE="IN" CFSQLTYPE = CF_SQL_INTERGER VARIABLE = BAR dbVarName = @param3> </CFSTOREDPROC> AND THIS: <CFQUERY Name="qryMyQuery" DATASOURCE="mydatasource">     {CALL usp_myproc("01/23/1971", "31")} </CFQUERY> Also, when the stored procedure returns a query, how would you access it through the first method?  The second method at least allows you to name the query so that you can access the resulting recordset. Thanks, Steve Durette Database Administrator/Web Developer Engineering/Construction System Support SBC/Ameritech ______________________________________________________________________ Dedicated Windows 2000 Server   PIII 800 / 256 MB RAM / 40 GB HD / 20 GB MO/XFER   Instant Activation · $99/Month · Free Setup   http://www.pennyhost.com/redirect.cfm?adcode=coldfusiona

Use the CFPROCRESULT name="MyQueryObject" inside the CFSTOREDPROC tags (normally the last one specified)... Using cfstoredproc tags eliminates a bunch of errors - in the CFQuery call, how do you know the Date is a proper format?  the cfprocparam tags handle any neccessary conversion (within reason) for you. Also, with cfstoredproc, you can have multiple recordsets returned, and have access to the Stored Procedures return value.  You can't do that with CFQuery. Shawn Grover I was just wondering if any one knew the difference between the following: <CFSTOREDPROC PROCEDURE="usp_myproc" DATASOURCE="mydatasource">     <CFPROCPARAM TYPE="IN" CFSQLTYPE = CF_SQL_DATE VARIABLE = FOO dbVarName = @param2>     <CFPROCPARAM TYPE="IN" CFSQLTYPE = CF_SQL_INTERGER VARIABLE = BAR dbVarName = @param3> </CFSTOREDPROC> AND THIS: <CFQUERY Name="qryMyQuery" DATASOURCE="mydatasource">     {CALL usp_myproc("01/23/1971", "31")} </CFQUERY> Also, when the stored procedure returns a query, how would you access it through the first method?  The second method at least allows you to name the query so that you can access the resulting recordset. Thanks, Steve Durette Database Administrator/Web Developer Engineering/Construction System Support SBC/Ameritech ______________________________________________________________________ Get Your Own Dedicated Windows 2000 Server   PIII 800 / 256 MB RAM / 40 GB HD / 20 GB MO/XFER   Instant Activation · $99/Month · Free Setup   http://www.pennyhost.com/redirect.cfm?adcode=coldfusionb

Thanks for the info, does anyone know if one is faster than the other? Steve ----- Excess quoted text cut - see Original Post for more ----- ______________________________________________________________________ Get Your Own Dedicated Windows 2000 Server   PIII 800 / 256 MB RAM / 40 GB HD / 20 GB MO/XFER   Instant Activation · $99/Month · Free Setup   http://www.pennyhost.com/redirect.cfm?adcode=coldfusionb

The quick difference between the <Cfquery> and <cfStoreproc> is you can deal with multiple recordset returned from the stored procedure which was not possible using <cfquery> .With <CfstoredProc> you can deal with the return values  from the stored procedures. The Only drawback I see using <cfStoredProc> is it can not handle the varbinary data which is possible with <cfquery> Thanks, Ninad ----- Excess quoted text cut - see Original Post for more -----

Another rather big difference is that inside CFquery SQL you can use CF and essentially have a dynamic SQL solution without the drawbacks of dynamic SQL. For some solutions this makes Ad-hock query faster than stored proc. TK The quick difference between the <Cfquery> and <cfStoreproc> is you can deal with multiple recordset returned from the stored procedure which was not possible using <cfquery> .With <CfstoredProc> you can deal with the return values  from the stored procedures. The Only drawback I see using <cfStoredProc> is it can not handle the varbinary data which is possible with <cfquery> Thanks, Ninad ----- Excess quoted text cut - see Original Post for more ----- MO/XFER >  Instant Activation · $99/Month · Free Setup >  http://www.pennyhost.com/redirect.cfm?adcode=coldfusionc