Filter fields in 'IN' clause using a variable

Question

I would like to do something like this :

DECLARE @list nvarhcar(200) SET @list = 'VALUE1,VALUE2,VALUE3'

Select * from foo where field in (@list)

Until today I solved using sp_executeSQL procedure, my own database function csv2table and subselect, but I think that is not very clean.

Is there any way to solve without use dynamic sql ? Is there a direct method?

I'm using Microsoft Sql Server 2005.

Thanks!

Answer 1

Would you please try as below: thanks

DECLARE @list nvarchar(200) 
SET @list = 'VALUE1,VALUE2,VALUE3'

SELECT * FROM foo WHERE CHARINDEX(',' + field + ',', ',' + @list + ',') > 0