With the total lack of information there is little anybody can do to help here. However, your dynamic sql is full of errors. You are not escaping your single quotes at all.
when c.loadset_group like 'M_PILOT' then 'R2'
When that is inside a string you need to escape your quotes.
when c.loadset_group like ''M_PILOT'' then ''R2''
Then I would ask why are you using LIKE instead of = for this? Why NOLOCK hints all over the place? It is impossible to tell if all that code is part of your proc or not because it has no begin - end. From trying to read through this I don't really see a need for dynamic sql at all.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/