using cfstoredproc or cfqueryparam 1

[sentill]

Hello,

Following is the query that I would like to use:

SELECT ALL PROPERTY.LEGAL_DESC, PROPERTY.COUNTY_CODE, calculation(PROPERTY_SALE.PROPERTY_ID, 'MDAS') FROM PROPERTY, PROPERTY_SALE WHERE(PROPERTY_SALE.PROPERTY_ID = PROPERTY.PROPERTY_ID) and ((property.certstatus in STATUS)) or (property.certstatus in STATUS2)) or (property.certstatus in STATUS3)) or (property.certstatus in STATUS4))) and (property.county_code = :COUNTY)

where calculation is a function in oracle.

Please guide me as to how to retrieve the recordsets by using cfstoredproc or cfqueryparam

Thanks
sentill

 

[pcorreia]

Since I'm not up on the stored procedure creation process in Oracle, here's how you can do it with <cfquery> and <cfqueryparam>. This gets you the advantages of bind parameters, but stored procedures would be even faster.

<cfquery name=&quot;qryProperties&quot; datasource=&quot;myDSN&quot;>
SELECT ALL 
   PROPERTY.LEGAL_DESC, 
   PROPERTY.COUNTY_CODE, 
   calculation(PROPERTY_SALE.PROPERTY_ID, 'MDAS')  
FROM 
   PROPERTY, 
   PROPERTY_SALE 
WHERE
   (PROPERTY_SALE.PROPERTY_ID = PROPERTY.PROPERTY_ID) and
   (
      (property.certstatus = 
         <cfqueryparam value=&quot;#STATUS#&quot;>) or 
      (property.certstatus = 
         <cfqueryparam value=&quot;#STATUS2#&quot;>) or
      (property.certstatus = 
         <cfqueryparam value=&quot;#STATUS3#&quot;>) or 
      (property.certstatus =
         <cfqueryparam value=&quot;#STATUS4#&quot;>)
   ) and 
   (property.county_code = 
      <cfqueryparam value=&quot;#COUNTY#&quot;>)
</cfquery>

<cfoutput query=&quot;qryProperties&quot;>
   <p>
      Legal Desc: #LEGAL_DESC#<br />
      County Code: #COUNTY_CODE#
   </p>
</cfoutput>