0

Possible Duplicate:
Parameterizing a SQL IN clause?

I am writing a C# program in which I send a query to SQL Server to be processed and a dataset returns. I am using parameters to pass information to the query before it is sent to SQL server. This works fine except in the situation below.

The query looks like this:

reportQuery = 
"
Select *
From tableName
Where Account_Number in (@AccountNum);
and Account_Date = @AccountDate
";

The AccountDate parameter works find but not the AccountNum parameter.

I need the final query to execute like this:

Select *
From tableName
Where Account_Number in ('AX3456','YZYL123','ZZZ123');
and Account_Date = '1-Jan-2010'

The problem is that I have these account numbers (actually text) in a C# string list. To feed it to the parameter, I have been declaring the parameter as a string. I turn the list into one string and feed it to the parameter. I think the problem is that I am feeding the paramater this: 

"'AX3456','YZYL123','ZZZ123'"

when it wants this 

'AX3456','YZYL123','ZZZ123'

How do I get the string list into the query using a parameter and have it execute as shown above?

This is how I am declaring and assigning the parameter.

SqlParameter AccountNumsParam = new SqlParameter();
AccountNumsParam.ParameterName = "@AccountNums";
AccountNumsParam.SqlDbType = SqlDbType.NVarChar;
AccountNumsParam.Value = AccountNumsString;

FYI, AccountNumString == "'AX3456','YZYL123','ZZZ123'"

Community
  • 1
  • 1
JK.
  • 1,144
  • 3
  • 15
  • 32
  • 1
    What version of SQL Server? If 2008 use Table Valued Parameters if not see [this answer](http://stackoverflow.com/questions/337704/parameterizing-a-sql-in-clause) for a variety of possibilities or [Erland Sommarskog - Arrays and Lists in SQL Server](http://www.sommarskog.se/arrays-in-sql.html) – Martin Smith Jan 01 '11 at 04:48
  • It's 2008. I will check that out and get back. Thanks! – JK. Jan 01 '11 at 04:49
  • I didn't like the chosen answer in the "duplicate" listed above. Otherwise, I would have closed this myself. Now I can't choose the answer I do like. Using Table Valued Parameters worked great. – JK. Jan 01 '11 at 17:52

2 Answers2

0

This is one of those things that isn't as easy as it would seem. One option would be to not use a parameterized query and build the query yourself, though that would easily open you up to SQL injection attacks, and therefore is generally not recommended.

Another option I've seen used is to pass the values in as a comma delimited string and then rely on a UDF to parse it out for you.

A third option (the one I like best of what is available), is to serialize the data into xml, pass it in as an xml data type, then have a sub-query after in IN keyword pull the values out of the xml. To be honest, I've only really done this with stored procedures before, so I can't say for sure if it would work just as well in a parameterized query, though I would think it will.

Look at my blog to see how to query XML in sql server: http://www.journeymandeveloper.com/ViewBlogPost/Query-XML-with-SQL-Server.aspx

Brian Ball
  • 12,268
  • 3
  • 40
  • 51
-1

How about:

cmd.Parameters.AddWithValue("@AccountNums", string.Join(",", accountNums.ToArray()));
Jayson Bailey
  • 994
  • 10
  • 23
  • John has a billion in reputation. I am tempted to take his word on this. – JK. Jan 01 '11 at 05:16
  • I should have put "'" + string.Join("','", accountNums.ToArray()) + "'", but that still doesn't work. Does anyone know why? – Jayson Bailey Jan 01 '11 at 20:55
  • because you are still doing `in (@OneSingleParam)`, it just so happens the value of `@OneSingleParam` that is being matched against happens to contain a string with commas in it. – Martin Smith Jan 01 '11 at 23:52
  • Actually this ms kb article explains why: http://support.microsoft.com/kb/555167 SqlParameter escapes the , characters to prevent sql injection attacks :) – Cohen Feb 21 '12 at 18:29