COLLATE DATABASE_DEFAULT不是IN ()子句

Question

我在存储过程中返回记录时遇到问题。没有抛出错误，但我认为来自一个服务器环境的数据在另一个服务器环境中不能很好地混合。

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

这是可行的：

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

显示8行，如下所示：

391000180   0001
391000189   0001
391000191   0001

这将失败：

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) 

Answer 1

不需要将所有这些字符串连接成逗号分隔的字符串。

IN接受返回单列待测试项目的子查询。只需使用

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 

此外，由于尾随空格在比较中并不重要，因此也无需使用RTRIM。如果ItemNumber是数字类型，则应该使用数字数据类型，而不是字符串。

Answer 2

在in子句中使用@items变量是没有意义的。它将此变量视为字符串。因此，除非它只有一个值，否则它不会像您想象的那样工作。

修复它的方法是将您需要的所有is放入另一个临时表中，然后将查询与此临时表连接起来。

SELECT ltrim(rtrim(ItemNumber )) AS ID INTO #Items FROM #ShipTemp;

SELECT   [ITEM_KEY],
         max([REVISION]) Rev
INTO     #rev
FROM     SERVER2.[BOMSystem].[dbo].[ITEM] i
         INNER JOIN #Items is ON (i.ITEM_KEY collate DATABASE_DEFAULT = is.ID)
WHERE    DATE_EFF_FROM  <= @shipedon
GROUP BY ITEM_KEY