WHERE ','+@ScratchNo+',' LIKE '%,'+CAST(ScratchNo AS NVARCHAR(32))+',%'

quote:

---

Originally posted by sunitabeck

SQL Server does not let you use a variable in the manner you are trying to use in the query. Your choices are:

a) To split the comma-separated string into individual tokens using a string splitter function into a (virtual) table and then join with that table. There are string splitter functions available on the web and in SQL Team archives - for example Fig 21 in here: http://www.sqlservercentral.com/articles/Tally+Table/72993/

b) Change your where clause to this:

WHERE ','+@ScratchNo+',' LIKE '%,'+CAST(ScratchNo AS NVARCHAR(32))+',%'

The second approach is simpler, but it may be less efficient because of the non-sargable predicate.

---