预先格式化的varchar变量在选择查询中不起作用。

Question

我正在尝试运行一个select查询，该查询在where子句中接收@Sites变量。查询当前返回为空，因为下面的行

AND S.[Name] IN (@Sites)

未按预期处理。以下是查询的摘录：

SET QUOTED_IDENTIFIER OFF

DECLARE @Month int
, @Sites varchar(MAX) = null

SET @Month = 8
SET @Sites = 'Bayside;Collaborative'
SET @Sites = "'" + REPLACE(@Sites,';',"','") + "'"


SELECT 
    CV.[Id] AS Id
    , ...
    , S.[Name] AS [Site]
    , ...
  FROM 
    [CalendarViews] CV
    INNER JOIN [Sites] S ON S.[Id] = CV.[SiteId]
  WHERE
    MONTH(CV.[DateAllocation]) = @Month
    AND S.[Name] IN (@Sites)
  ORDER BY 
     S.[Name]

当我运行查询时，替换

AND S.[Name] IN (@Sites)

为

AND S.[Name] IN ('Bayside','Collaborative')

查询的工作方式与预期相同。有人能告诉我我错过了什么吗？谢谢。

Answer 1

@Sites是一个字符串，而不是数组。所以你实际上是在寻找：

S.[Name] IN ('''Bayside'',''Collaborative''')
-------------^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
-- this is one single string

我强烈建议您传递表值参数，而不是传入逗号或分号分隔的字符串。首先，在数据库中创建以下内容：

CREATE TYPE dbo.Sites AS TABLE(Site NVARCHAR(255));

然后，您的程序会说(忽略不太相关的部分)：

ALTER PROCEDURE dbo.ProcedureName
  @Sites dbo.Sites READONLY
AS
BEGIN
  SET NOCOUNT ON;

  SELECT ...
  FROM dbo.Sites AS s
  INNER JOIN @Sites AS tvp
    ON s.[Name] = tvp.Site;
END
GO

然后，您的代码将传递一个结构化参数(我假设这组站点最初来自DataTable或其他集合)：

SqlCommand cmd = new SqlCommand("dbo.ProcedureName", conn);
c2.CommandType = CommandType.StoredProcedure;
SqlParameter tvp = cmd.Parameters.AddWithValue("@Sites", DataTableName);
tvp.SqlDbType = SqlDbType.Structured;
...

如果您真的想以缓慢的、老式的方式来完成这个任务，您可以创建一个UDF来拆分字符串：

CREATE FUNCTION dbo.SplitStrings_XML
(
   @List       NVARCHAR(MAX),
   @Delimiter  NVARCHAR(255)
)
RETURNS TABLE
WITH SCHEMABINDING
AS
   RETURN 
   (  
      SELECT Item = y.i.value('(./text())[1]', 'nvarchar(4000)')
      FROM 
      ( 
        SELECT x = CONVERT(XML, '<i>' 
          + REPLACE(@List, @Delimiter, '</i><i>') 
          + '</i>').query('.')
      ) AS a CROSS APPLY x.nodes('i') AS y(i)
   );
GO

然后，您的查询是(再次忽略了远不相关的部分)：

  INNER JOIN dbo.SplitStrings_XML(@Sites, N';') AS f
    ON s.[Name] = f.Item;

现在，XML可能不是这里的安全方法，但是在不提供更慢的方法或辅助对象(如numbers表)的情况下，复制是最简单的方法。有关其他备选方案，以及关于为什么不想这样做的更多信息，请参见：

• 拆分字符串的正确方式--或下一个最佳方式
• 分裂字符串:后续研究
• 分裂字符串:现在使用较少的T