> I have a query that I had been just comparing one value
> so I could use a cfqueryparam of varchar, now that same
> variables may be passing something like 203,197 meaning
> that a sales rep belongs to each of those groups. So I
> need to change my query to something IN ('203','197'),
> but cfqueryparam doesn't let me do this. Do I need to
> use something else besides cfqueryparam or is there a
> setting I am not seeing that will allow me to use
> cfqueryparam and this?
No, you won't be able to use CFQUERYPARAM with this, nor is there a simple
workaround other than simply evaluating the value in your SQL. The problem
is that there's no SQL list datatype, and of course you need to use a list
here. The same problem exists with stored procedures - you can't pass a list
as a parameter and have SQL recognize it and use it as a list. So,
typically, if you had to address this problem within a stored procedure, you
might use the ability to execute SQL dynamically within the procedure.
Here's an example (note that there may well be syntax errors, since I just
made it up):
CREATE PROCEDURE mysp
@mylist varchar(255)
AS
sp_executesql "SELECT * FROM mytable WHERE myid IN (" + @mylist + ")"
GO
Dave Watts, CTO, Fig Leaf Software
http://www.figleaf.com/
voice: (202) 797-5496
fax: (202) 797-5444
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~|
Archives: http://www.houseoffusion.com/cf_lists/index.cfm?forumid=4
Subscription:
http://www.houseoffusion.com/cf_lists/index.cfm?method=subscribe&forumid=4
FAQ: http://www.thenetprofits.co.uk/coldfusion/faq
Your ad could be here. Monies from ads go to support these lists and provide more
resources for the community. http://www.fusionauthority.com/ads.cfm
