Using the dynamic IN clause in MSSQL

Question

Why the next SQL brings me nothing

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

As long as it select * from MYTABLE where status in('Closed','Open')picks me strings

Answer 1

You can if you want to do some dynamic SQL, but I think it is not very 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 a value exists in the database 'Closed','OPEN'. Values ​​do not expand.

If you are using SQL Server 2008 or later, you can use the Parameter Values ​​Table to achieve the same.