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.