> 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.
The only way I managed get around this is to use String Searching functions.
In SQL server it looks like this:
select pet_stuff1, pet_stuff2
from mypettable
where PATINDEX('%,' + CAST( pet_type AS varchar )+',%', ',' +
inTypeOfPets + ',' ) <> 0
Basically the idea is to CAST the column you want to use in the where
clause (in your case pet_type), concatenate it a comma on it's left
and right (i.e. ','+pet_type+',') and patern match this against your
list which you also enclose within commas.
Beware, this is not a very performant solution since operations must
be made against the searched column, but in the case of small tables
or used with other restricting clauses it comes in very handy!
You could also, dynamically create an SQL statement within your Stored
Proc (see EXECUTE(prepared statement) but that would take out the
benefits of using a Stored Proc.
Hope this helps,
--
Marc
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~|
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:182922
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=89.70.4
Donations & Support: http://www.houseoffusion.com/tiny.cfm/54
