SQL Server sp_prepare with in-clause

Question

I have the following but it is not working:

declare @p1 int
exec sp_prepare @handle=@p1 output,@params=N'@id int,@name nvarchar (MAX)',
    @stmt=N'SELECT [id],[name] FROM [dbo].[channel] WHERE [name] IN (@name)'
select @p1
exec sp_execute @handle=1,@id=NULL,@name=N'''nisse'',''pelle'''

Is there a way of doing it like above or am I forced to parameterize every value in the array?

I can not use other tables, functions or change the query other than the where and the @name parameter.

possible duplicate of http://stackoverflow.com/questions/697519/split-function-equivalent-in-t-sql - you want to convert your comma separated parameter into a temp table or table variable, and use the temp table in your IN clause. — Stan, Jul 07 '15 at 02:51

score 0 · Answer 1 · answered Jul 07 '15 at 02:53

You may find that parameterizing individual values is best, but there are other solutions.

A simple one is:

where ',' + @name + ',' like '%,' + name + ',%'

However, this cannot make use of indexes.

Another approach is to get a split() function from the web (lots are out there: google "split sql server"):

where name in (select val from dbo.split(@name, ',') as s(val))

SQL Server sp_prepare with in-clause

1 Answers1