SQL parameter in where clause that allows for all, specific, or multiple values

Question

In SQL Server 2008 I need to be able to pass in a parameter that allows for all values, specific values, or multiple values. Below sql works fine when the parameter is "All". And works fine with a single value. But with multiple values I get "Msg 4145, Level 15, State 1, Line 7 An expression of non-boolean type specified in a context where a condition is expected, near ','." I've tried a number of things but not getting it.

--Works fine
Declare @sql varchar(8000)
,@p_CODE varchar(100)
,@v_CODE VARCHAR(100)
Set @p_CODE = ('''All''')
Set @v_CODE = @p_CODE
select @sql = 'SELECT distinct
 CODE
 FROM TABLE 
 WHERE '+@v_CODE+' in ('+'''All'''+')
  or CODE in ('+@v_CODE+')'
Execute (@sql)
--Returns all records 
 
 
--Works fine  
Declare @sql varchar(8000)
,@p_CODE varchar(100)
,@v_CODE VARCHAR(100)
Set @p_CODE = ('''ABCD''')
Set @v_CODE = @p_CODE
select @sql = 'SELECT distinct
 CODE
 FROM TABLE 
 WHERE '+@v_CODE+' in ('+'''All'''+')
  or CODE in ('+@v_CODE+')'
Execute (@sql)  
--Returns records with ABCD


--Returns error as noted
Declare @sql varchar(8000)
,@p_CODE varchar(100)
,@v_CODE VARCHAR(100)
Set @p_CODE = ('''ABCD'',''EFGH''')
Set @v_CODE = @p_CODE
select @sql = 'SELECT distinct
 CODE
 FROM TABLE 
 WHERE '+@v_CODE+' in ('+'''All'''+')
  or CODE in ('+@v_CODE+')'
Execute (@sql)

Answer 1

The left side of an IN should be an Expression, but your code made it not, it is:

SELECT distinct
    CODE
    FROM    TABLE 
    WHERE   'ABCD','EFGH' in ('All')
        or CODE in ('ABCD','EFGH')

Which you can see it is your first in has the problem not the second.

You need to change you code logic like:

Declare @sql varchar(8000)
,@v_CODE VARCHAR(100)
select @sql = 'SELECT distinct
    CODE
    FROM    TABLE 
    ' + CASE WHEN @v_CODE IS NULL THEN '' ELSE ' WHERE CODE in ('+@v_CODE+')' END

Execute (@sql)

And also I believe there are some better way to perform this. If the collection is too big the IN may cause performance issue.

Answer 2

This works (just reverse the in)...

Declare @sql varchar(8000)
,@p_CODE varchar(100)
,@v_CODE VARCHAR(100)
Set @p_CODE = ('''ABCD'',''EFGH''')
Set @v_CODE = @p_CODE
select @sql = 'SELECT distinct
    CODE
    FROM    TABLE 
    WHERE   CODE in ('+@v_CODE+')
        OR (''All'' in ('+@v_CODE+')
            AND CODE in (SELECT DISTINCT
                            CODE
                            FROM TABLE ))'
Execute (@sql)