Sql-server – SQL Where clause pass list to a column

sql-serversql-server-2008sql-server-2008-r2

I have some table DOCUMENTS that have a column TYPE_ID and a table named TYPES
Now I want to make a query which filter on my DOCUMENTS all the document that have a list of TYPE_ID that can be a single element or sort of elements

The normal query is:

Select * From DOCUMENTS Where TYPE_ID = myValue

But when it can be a list of known elements the wuery looks like

Select * From DOCUMENTS Where TYPE_ID = myValue1 or TYPE_ID = myValue2 or TYPE_ID = myValue3

But if you have n elements…. how can this query be done?

Best Solution

You want the IN Clause :

SELECT *
FROM  Documents 
WHERE Type_ID IN (myValue1, myValue2, myValue3)

As a side note, if you're new to this clause you cannot do something like :

@myVar = '1,2,3'
select * FROM Documents where Type_ID in (@myVar)

There are solutions for something like this. But I point it out because it seems to come up when people are new to the IN clause.