1

Is there a way to do the following in SQL Server:

DECLARE @list nvarchar(MAX) = '1, 2, 3, 4, 5, 6';
SELECT * FROM table WHERE ID IN ( @list );

-- Column ID is type bigint

Error:

Error converting data type nvarchar to bigint.

reformed
  • 4,505
  • 11
  • 62
  • 88
  • possible duplicate of [Parameterizing an SQL IN clause?](http://stackoverflow.com/questions/337704/parameterizing-an-sql-in-clause) or so many similar questions... – OzrenTkalcecKrznaric Jul 19 '13 at 18:59

2 Answers2

7

You don't want to convert it to an int. Instead, use like for the comparison:

select *
from table
where ', '+@list+', ' like ', '+cast(id as varchar(255)) + ', ';

This has the downside that the query will not use indexes. If that is important, then you can use dynamic SQL:

DECLARE @list nvarchar(10) = '1, 2, 3, 4, 5, 6';
declare @sql nvarchar(max) = 'SELECT * FROM table WHERE ID IN ('+ @list +')';

exec sp_executesql @sql;
Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
5

use Dynamic SQL.

DECLARE @SQLQuery AS NVARCHAR(500)
DECLARE @list nvarchar(10) = '1, 2, 3, 4, 5, 6';

SET @SQLQuery = 'SELECT * FROM table WHERE ID IN (' + @list + ')'
EXECUTE(@SQLQuery)
John Woo
  • 258,903
  • 69
  • 498
  • 492