Passing a parameter to a select select SQL IN statement is weird.

Question

I have the following query that returns 2 records (in the DataSet query builder)

SELECT EmpID, Name, id FROM Users WHERE (CAST(id AS Varchar(20)) IN ('5688','5689')) 

Now, if I make the same request, passing this parameter instead of code: String param = "'5688', '5689'"; it returns null.

 WHERE (CAST(id AS Varchar(20)) IN (@param)) 

I tried to take off from the very first and last, "but that did not change the situation.

!!! id - unique PK !!!

Anyone got it?

Answer 1

The solution I found is quite simple, it works like a charm, and there is no need for sps or other functions;

SQL:

 SELECT whatever FROM whatever WHERE (PATINDEX('%''' + CAST(id AS Varchar(20)) + '''%', @param) > 0) 

FROM#:

 String param = "'''1234'',''4567'''"; dataTable1 = tableAdapter1.getYourValues(param); 

Answer 2

A variable is not allowed in an IN clause.
You expect that comma-delimited string values ​​you can use the split function (custom and non-standard) to join with the source tables:

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=326300&SiteID=1

For more information you can visit this

Answer 3

('5688','5689') is an array of values.

Definition String param = "'5688','5689'"; and using it as (@param) makes the string ('5688','5689') string. Which one is not working.

Answer 4

Bibhas is faithful. For me it worked:

string param="'1234','4567'"; we cannot use a parameter as an SQL parameter (@param).

command = new SqlCommand("SELECT * FROM table WHERE number IN (" + param + ")", connection);

command.ExcecuteReader();