On Thu, 28 Oct 2004 18:09:46 -0700, Casey C Cook <[EMAIL PROTECTED]> wrote: > I have a comma delimited list returned from a multi select field on my > input form. I then take the list and use the following code: > > ListQualify(#form.TypeofPets#,"'",",","ALL") > > which gets the user selected result set such as the following: > > 'Dogs','Cats','Horses' > > I then pass this variable into a stored procedure: > > <cfstoredproc procedure="FAVORITE_PET_PKG.GET_PETS" datasource="pet" > returncode="No"> > <cfprocparam cfsqltype="CF_SQL_VARCHAR" value="#TypeOfPets#" > type="In" dbvarname="inTypeOfPets"> > <cfprocresult name="FavPets"> > </cfstoredproc> > > The stored procedure query looks like the following: > > select pet_stuff1, pet_stuff2 > from mypettable > where pet_type in (inTypeOfPets) > > The problem is even though my input string looks like > 'Dogs','Cats','Horses' the ticks are getting stripped out of the variable > (Dogs,Cats,Horses), how do I retain the ticks? inTypeOfPets is defined as > a varchar2. Im using CF MX and oracle 9.2. >
There's currently no way to do this, sadly. There is no "list" datatype in stored procs, which is essentially what you're passing in. Somebody posted a workaround a long time ago for SQL Server that essentially looped over the list of values passed in, so check the archives if you're interested. I'm sure it could be ported to Oracle. That being said, I'd just stick with inline SQL using the cfqueryparam's list attribute. It'll save you a lot of time. Regards, Dave. ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| Purchase from House of Fusion, a Macromedia Authorized Affiliate and support the CF community. http://www.houseoffusion.com/banners/view.cfm?bannerid=34 Message: http://www.houseoffusion.com/lists.cfm/link=i:4:182899 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4 Donations & Support: http://www.houseoffusion.com/tiny.cfm/54
