3

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.

OMG Ponies
  • 325,700
  • 82
  • 523
  • 502
chris
  • 36,094
  • 53
  • 157
  • 237

4 Answers4

2

There are two ways of accommodating Dynamic IN lists:

  1. Convert the comma separated list into a derived table (AKA inline view)
  2. Use dynamic SQL

Non-Dynamic

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:

  1. Will never be able to use an index
  2. Won't be able to match the first or last parameter in the list, because commas are required on either end to match

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

Dynamic SQL

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.

OMG Ponies
  • 325,700
  • 82
  • 523
  • 502
0

Same answer as SQL Server, already asked here: Parameterize an SQL IN clause

Community
  • 1
  • 1
Nick Craver
  • 623,446
  • 136
  • 1,297
  • 1,155
  • That is the worst performing avenue to take. Last time I saw a reference to that answer, it was torn to shreds. The best approach is to convert the string/etc list representation into a table, and then join onto it. – OMG Ponies Feb 17 '10 at 19:15
  • Can you point to that discussion? I understand there are compromises in this approach, but not sure if creating a temp. table for each query is going to be an easy fix. – chris Feb 17 '10 at 20:02
  • @OMG, @chris - Sorry, I just re-read this, don't look at the accepted answer, look down one that's top voted. – Nick Craver Feb 17 '10 at 21:28
0

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)
Álvaro González
  • 142,137
  • 41
  • 261
  • 360
0

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:

Dynamic query with HibernateCritera API & Oracle - performance

I cannot pretent it is my trick, as it came from Tom Kyte's blog, who is an Oracle guru!

Community
  • 1
  • 1
Stephen ODonnell
  • 4,441
  • 17
  • 19