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

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?

stackoverflow.com/questions/4613273/…

benni_mac_b
– benni_mac_b

2013-04-08 19:30:39 +00:00
Commented Apr 8, 2013 at 19:30 — benni_mac_b
– benni_mac_b, Commented Apr 8, 2013 at 19:30

mellamokb · Accepted Answer · 2013-04-08 19:29:46Z

3

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 8, 2013 at 19:29

mellamokb

56.8k12 gold badges111 silver badges138 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Bill Software Engineer Over a year ago

My query is really really long... is there anyway you can separate the @param1 dynamic query into it's own query?

Collectives™ on Stack Overflow

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

1 Answer 1

1 Comment

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Linked

Related