How do I use SQL IN statement of string values for int column?

Question

For circumstances out of my hand, I have a string of values:

@param1 = "1,2,3,4";

How do I do IN statement to check if an ID is in my string @param1?

so, it would look something like this:

select * from table1 where ID IN (@param1);

Normally, I guess I should have this:

select * from table1 where ID IN (1,2,3,4);

But unfortunately, under my circumstances, I simply don't have that, instead, I have just one parameter of type string of all my IDs concatenated, how do I select the rows from that?

http://stackoverflow.com/questions/4613273/sql-use-comma-separated-values-with-in-clause — benni_mac_b, Apr 08 '13 at 19:30

score 2 · Accepted Answer · answered Apr 08 '13 at 19:29

2

You can use a dynamically-generated SQL statement (DEMO):

declare @sql varchar(max)

set @sql = 'select * from table1 where ID IN (' + @param1 + ')'
exec(@sql)

The idea is to build a SQL statement in a string variable by concatenating the ID list into a SQL statement. The final statement looks like your expected query:

select * from table1 where ID IN (1,2,3,4)

Then you can run the contents of the string variable as a SQL command using exec.

answered Apr 08 '13 at 19:29

mellamokb

56,094
12
110
136

My query is really really long... is there anyway you can separate the @param1 dynamic query into it's own query? – Bill Software Engineer Apr 08 '13 at 19:31

How do I use SQL IN statement of string values for int column?

1 Answers1