1

Possible Duplicate:
Parameterizing a SQL IN clause?

How do I translate the following query to a prepared statement (if possible..)?

string allemails = "[email protected], [email protected], [email protected]"; //etc...
string query = "select UNIQUE_ID users where E_MAIL in (" + allemails + ")";
//run query....

Can I do something like:

OdbcCommand cmd = sqlConn.CreateCommand();
cmd.CommandText = "select UNIQUE_ID users where E_MAIL in (?)";

If so, what is the parameter I should add to the cmd parameters collection, and if it's varchar, how can I ask for unlimited size?

Community
  • 1
  • 1
Nili
  • 1,963
  • 3
  • 20
  • 39

2 Answers2

1

That you are using the OdbcCommand would indicate that you are using a database other than SQL Server. SQL Server 2008 introduced table valuded parameters which might have been a solution.

Assuming that the database that you are using does not support this feature, a split function such as the one that gjvdkamp suggested would be an option.

If your database support dynamic sql (SQL Server provides the sp_executesql stored procedure for this purpose) you could also consider using that.

A final option that you might want to consider (not very elegant) would be to allow for a limited number of email addresses and allow each to be passed via its own parameter. You would then use them in the where clause as follows.

where E_MAIL in (@Address1, @Address2, @Address3)
Scott Munro
  • 13,369
  • 3
  • 74
  • 80
  • Thank you for the very detailed reply! However, the last method is indeed not elegant since I build my allemails list dynamically... – Nili Feb 13 '11 at 14:46
0

Hmm, good one, it don;t think you can do it like that.

My take would be to rewrite the query to use a join against a set of options, and you generate that set of options from a split function.

For a split look here: http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=50648

Then the SP would look something like this 

Create proc GetUsersByEmail
 @EmailList varchar(8000)
as

  select unique_id
  from users u
       inner join split(@emaillist,',') e on u.e_mail = e.data

Actually you could also pass the sql directly without creating an SP in the DB, but I'd go for a SP.

Regards GJ

gjvdkamp
  • 9,929
  • 3
  • 38
  • 46