I have a query with a where clause that looks like:
WHERE field IN ( 1, 2, 3 )
Is possible to parametrize this so it looks something like:
WHERE field in ( :list )?
How do you create the :list parameter, assuming it's a list of ints, that could be anywhere from 1 to 10 ints?
ASP.net, webforms if that makes a difference.
Same answer as SQL Server, already asked here: http://stackoverflow.com/questions/337704/parameterizing-a-sql-in-clause
As far as I know, you cannot. You'd be mixing values and operators in the same place.
However, it should be fairly simple to create automatically an array of parameters in ASP.NET and get this dynamically:
WHERE field IN (:list1, :list2, :list3, :list4)
There are two ways of accommodating Dynamic IN lists:
Most prefer the non-dynamic SQL approach - this link provides various ways to do it. The biggest reason to use this over:
WHERE :list LIKE '%,' || t.column || ',%'
...is that the above:
The simple fact is, it won't work as intended. A regex, supported on Oracle 10g+, would allow for conditional checking on the column but still faces the problem of rendering an index as moot
Mention "dynamic SQL", and you likely will be hounded about SQL injection attacks. Using a bind variable alleviates the concern.
That said, dynamic SQL requires the least change to the query.
This question 'how do I bind an in list' seems to come up a lot.
The easiest 'trick' to do this in Oracle is using the answer I posted here:
I cannot pretent it is my trick, as it came from Tom Kyte's blog, who is an Oracle guru!