COLLATE DATABASE_DEFAULT agaisnt an IN () clause

Question

I have a problem returning records in a sproc. No error is thrown but I think data coming from one server environment is not mixing well in another.

set @shipedon =  YEAR(@shipdate) * 10000 + MONTH(@shipdate) * 100 + DAY(@shipdate)

This works:

SELECT   [ITEM_KEY],
         max([REVISION]) Rev
FROM     SERVER2.[BOMSystem].[dbo].[ITEM]
WHERE    ITEM_KEY collate DATABASE_DEFAULT in
         ('391000180', '391000189', '391000191', '391000201',
          '391000214', '391000215', '391000216', '391000226')
AND      DATE_EFF_FROM <= @shipedon 
GROUP BY ITEM_KEY

shows 8 rows like:

391000180   0001
391000189   0001
391000191   0001

This fails:

SELECT   [ITEM_KEY],
         max([REVISION]) Rev
INTO     #rev
FROM     SERVER2.[BOMSystem].[dbo].[ITEM] 
WHERE    ITEM_KEY collate DATABASE_DEFAULT in (@items)                                                       
AND      DATE_EFF_FROM  <= @shipedon
GROUP BY ITEM_KEY

SELECT * from #rev shows no results.

SELECT @items = SUBSTRING(
(SELECT distinct ',' +''''+ ltrim(rtrim(ItemNumber )) +'''' 
collate DATABASE_DEFAULT 
FROM #ShipTemp   
FOR XML PATH('')),2,20000) 

Solution

There is no need to concatenate all of them into a comma delimited string.

IN accepts a sub query that returns a single column of items to be tested. Just use

SELECT [ITEM_KEY],
       max([REVISION]) Rev
INTO   #rev
FROM   SERVER2.[BOMSystem].[dbo].[ITEM]
WHERE  ITEM_KEY COLLATE DATABASE_DEFAULT IN (SELECT LTRIM(ItemNumber )
                                             FROM   #ShipTemp)
       AND DATE_EFF_FROM <= @shipedon
GROUP  BY ITEM_KEY 

Also no need to RTRIM as trailing spaces aren't significant in a comparison and if ItemNumber is numeric you should use a numeric datatype, not a string.