Tuesday, August 18, 2009

Pass parameter to IN clause in SQL Server

Problem:

Suppose you have a query statement (either in Store procedure or Sql state) something like:

SELECT * FROM
myTable WHERE
Field IN(@DelimiterString)

and you want to execute this by passing a delimiter string '1, 2, 3, 4' to execute the query. Unfortunately, you can't do this. You will get error message "Conversion failed when converting the nvarchar value 'xxx,xxx' to data type int."

Solution:

There are several solutions to this problem something like split the value and save to temp table and read them back in IN clause. But i would like to introduce a simple solution by using dynamic sql. I may transform my query to:

exec('SELECT * FROM
myTable WHERE
Field IN(' + @DelimiterString + ')')


Finally, call your store procedure or sql statement accordingly with your delimiter parameter

2 comments:

Getting the best out of Hajj said...

Thank you very much.

Your post saved me a lot of trouble

Eric said...

Thanks a lot! A simple solution that was very helpful.