sql - using dynamic IN clause in MSSQL

Question

Why the following SQL does not fetch me anything

DECLARE @Status AS VARCHAR(400)
SET @status = '''Closed'',''OPEN'''
select * from MYTABLE where status in(@status)

While as select * from MYTABLE where status in('Closed','Open') fetches me rows

Answer 1

Your first question checks if the value 'Closed','OPEN' exists in the database. The values is not expanded.

If you use SQL Server 2008 or later you can use Table Valued Parameters to achieve the same thing.