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

You can if you want do some dynamic SQL but I think it is not really competitive..

  DECLARE   @Status nVARCHAR(400),
            @SQL nvarchar(500)

SET @status = '''Closed'''+','+'''OPEN'''
set @SQL = '
select * from [MYTABLE] where status in('+@status +')'

 exec sp_executesql @SQL
GO

Answer 2

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.