1

How do I pass paramter values for a command where SQL Text contains IN. i.e. My command SQL text is something like SELECT * FROM USERS WHERE USERID IN (1,2,3).

There are plenty of examples like MSDN has but coudn't find one to pass values for IN. Tried taking a variable and set values as a single string but SQL wont work that way.

Cannon
  • 2,725
  • 10
  • 45
  • 86

3 Answers3

0

The short answer is there's no good way to do this.

If you're indeed only working with a list of numeric values, you probably don't need to use parameters as this feature is mainly designed to prevent SQL injection attacks. However, if you have an array of ints, and use that to build your query, you're safe.

The only other way I can think of would be to add one parameter per item. For example:

SELECT * FROM USERS WHERE USERID IN (@p1, @p2, @p3)

Then, loop through your array in C# and create a parameter for each item.

Hope this helps!

Mike Christensen
  • 88,082
  • 50
  • 208
  • 326
0

This has been asked many, many times. Two ways spring to mind:

If you have SQL2008, you can pass a "table value parameter" (tvp), where your table parameter has the values for your "in" clause, then have a "where" clause that checks values are "in (select whatever from tvp)".

If you do not have SQL2008, you can use a "common table expression" (cte), such as...

declare @t table (xyz varchar(100))
insert into @t values ('hello')
insert into @t values ('there')
insert into @t values ('world')
insert into @t values ('10')
insert into @t values ('20')
insert into @t values ('30')

declare @a as varchar(120)
set @a = 'hello,10,30'

;with cte as
(
    select cast(null as varchar(max)) as 'v', 1 as s, charindex(',' , @a, 0) as e
union all
    select cast(substring(@a, s, e-s) as varchar(max)), e+1, charindex(',' , @a + ',', e+1)
    from cte where e!=0
)
select *
from @t
where (xyz in (select v from cte where v is not null))

In this example (above), the string @a is the comma separated list of values you want for your "in" clause, and the (recursive) cte just strips out the values, one by one. Of course, this is just a quick example and would probably need checks for empty strings, successive commas, and the like.

Of course, the usual caveats apply with regard to using SQL to (effectively) do string manipulation.

Regards, Ross

Black Light
  • 2,358
  • 5
  • 27
  • 49
0

You should have a UDF which converts a (for example) csv string into a table of values. Then, your query can be some thing like:

select * from yourTable where yourField IN ( select yourColumn from dbo.yourUDF(yourCSV) )

odiseh
  • 25,407
  • 33
  • 108
  • 151
  • Yes. This option was always there. I was just wondering if there is any I can omit this exercise as I dont wanted to modify the coming SQL except passing paramters and system replacing with their values. – Cannon Dec 08 '11 at 20:10