Using Dynamic IN Clause In MSSQL

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 selec

Solution 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

Solution 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.