-1

Possible Duplicate:
Parameterizing an SQL IN clause?
SQL how to get an equality comparator on a list of ints

I have a string in C# like this:

string listOfNumbers = "12,56,90,101"; //of course, this string could be different

and C# code

sqlCommand.Parameters.Add("@numbers", listOfNumbers);
SqlDataReader dataReader = sqlCommand.ExecuteReader();

But sqlCommand is binded to a stored procedure and inside is a sql statement like

SELECT * FROM TABLE1 WHERE COLUMN1 IN(@numbers)

@numbers is NVARCHAR(30) data type.

But when the breakpoint is at SqlDataReader dataReader = sqlCommand.ExecuteReader(); line then the VS2010 throws me an exception Incorrect syntax near my_stored_procedure.

How to "convert" that string which contains numbers sepparated by comma in integers for IN operator ?

Maybe, the error is caused by SELECT * FROM TABLE1 WHERE IN("12,56,90,101") and the error should be that could not convert from varchar to integer.

Community
  • 1
  • 1
Snake Eyes
  • 16,287
  • 34
  • 113
  • 221
  • @AndriyM: wrong. Useless. Try stop posting if you don't know. – Snake Eyes Apr 19 '12 at 13:02
  • @MichaelSwan: Well, the immediate issue is indeed different. You are missing an expression before `IN` (typically a column, but not necessarily), i.e. it should be something like `SELECT * FROM TABLE1 WHERE SomeColumn IN (...)`. But after you fix that, you'll most likely face the issue of parametrising the `IN` list. – Andriy M Apr 19 '12 at 13:07
  • 1
    @MichaelSwan - try being polite to people who are trying to help you. Your fundamental issue is the same as it is in all of the previous questions. You somehow believe that SQL magically inspects the interior of a string, sees commas, and decides to treat it as a number of separate strings, despite no other programming language ever doing that. – Damien_The_Unbeliever Apr 19 '12 at 13:17
  • Depends on your DBMS, eg MSSQL 2005 you need to use dynamic sql such as execute_sql, [http://www.sommarskog.se/dynamic_sql.html#sp_executesql but with MSSQL 2008 you can use a table-valued parameter, http://msdn.microsoft.com/en-us/library/bb675163.aspx – Chris Chilvers Apr 19 '12 at 13:21
  • @AndriyM This is not a duplicate of the refered anwsers. in one of them the list values are stings and here integers and probably PK so converting them to a string and making a full scan with LIKE, is a big drawback. The other anwser is about TSQL, which difers a lot from a parametrized query from c. – Saic Siquot Apr 19 '12 at 13:45
  • 1
    @LuisSiquot: I agree, that question isn't an exact duplicate *by itself*, but it contains *many* different answers, some of which are applicable to integers too. And if you consider its very populous *Linked* section, I think the total number of suggestions that my link gives a shortcut to should be enough for the OP to choose from. – Andriy M Apr 19 '12 at 14:32

1 Answers1

0

C# code:

string listOfNumbers = "12|56|90|101";

SQL query:

SELECT  *
FROM    TABLE1
WHERE   '|' + @numbers + '|' LIKE '%|' + CAST(COLUMN1 AS varchar) + '|%'
weenoid
  • 1,156
  • 2
  • 11
  • 24