Tuesday, August 18, 2009

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: