SQL存储过程IN子句varchar数组

Question

我有一个c#程序，想给存储过程一个参数。我的c#程序中的字符串类似于"食物"、"衣服"、"血液"。

ALTER PROCEDURE [dbo].[GetLocations]
    @longMax float =100,
    @longMin float=0,
    @latMax float=100,
    @latMin float=0,
    @categoryFilter char(200) = ''
AS
BEGIN
    SET NOCOUNT ON;
    SELECT *
    FROM dbo.Locations
    WHERE longitude BETWEEN @longMin AND @longMax
      AND latitude BETWEEN @latMin AND @latMax
      AND subCategory in (@categoryFilter) 
END

结果应该给我3个条目，但结果是空的。我试着用另外两个来转义参数，但结果没有改变。

它可以处理其他列上的int值，但不能处理字符。

如果有人能帮忙，那就太好了：）

Answer 1

由于in clause处理数据集，因此您得到的结果集为空。你正在传递一个200个单词的字符串。您的查询实际上是在比较subCategory和categoryFilter

你可以在管理工作室中通过提供如下来明确地尝试

SELECT *
FROM dbo.Locations
WHERE longitude BETWEEN @longMin AND @longMax
AND latitude BETWEEN @latMin AND @latMax
AND subCategory in ('abc','bcd','cde');

abc-你的第一类过滤器，同样bcd你的第二类过滤器，依此类推。如果@categoryfilter中有一组数据用逗号（，）分隔，则首先拆分该字符串，然后使用IN Clause

Answer 2

我认为您需要拆分字符串参数-

DECLARE @categoryFilter VARCHAR(200) = 'Food,Clothes,Blood'
SELECT t.c.value('.', 'VARCHAR(50)')
FROM (
     SELECT ID = CAST ('<M>' + REPLACE(@categoryFilter, ',', '</M><M>') + '</M>' AS XML)
) r 
CROSS APPLY ID.nodes ('/M') t(c)

您的查询-

ALTER PROCEDURE [dbo].[GetLocations] 
@longMax FLOAT = 100,
@longMin FLOAT = 0,
@latMax FLOAT = 100,
@latMin FLOAT = 0,
@categoryFilter VARCHAR(200) = 'Food,Clothes,Blood'
AS
BEGIN
     SET NOCOUNT ON;
     SELECT *
     FROM dbo.Locations
     WHERE longitude BETWEEN @longMin AND @longMax
          AND latitude BETWEEN @latMin AND @latMax
          AND (
               @categoryFilter = '' 
               OR 
               subCategory IN (
                    SELECT t.c.value('.', 'VARCHAR(50)')
                    FROM (
                         SELECT ID = CAST ('<M>' + REPLACE(@categoryFilter, ',', '</M><M>') + '</M>' AS XML)
                    ) r 
                    CROSS APPLY ID.nodes ('/M') t(c))
          )
END

Answer 3

试试这个

ALTER PROCEDURE [dbo].[GetLocations]
@longMax float =100,
@longMin float=0,
@latMax float=100,
@latMin float=0,
@categoryFilter char(200) = ''
AS
BEGIN
    SET NOCOUNT ON;
   SET @categoryFilter =  REPLACE(@categoryFilter,',',''',''')
   DECLARE @QUERY VARCHAR(MAX)
   SET @QUERY = 'SELECT *
    FROM dbo.Locations
    WHERE longitude BETWEEN @longMin AND @longMax
      AND latitude BETWEEN @latMin AND @latMax
      AND subCategory in ('''+RTRIM(@categoryFilter)+''')'
    --PRINT (@QUERY) 
    EXEC (@QUERY) 

END

Answer 4

尝试修剪传入字符串->RTRIM（@string）。

如果你使用的是char，你总是需要修剪。

Answer 5

您可以使用LIKE：

ALTER PROCEDURE [dbo].[GetLocations]
    @longMax float =100,
    @longMin float=0,
    @latMax float=100,
    @latMin float=0,
    @categoryFilter char(200) = ''
AS
BEGIN
    SET NOCOUNT ON;
    SELECT *
    FROM dbo.Locations
    WHERE longitude BETWEEN @longMin AND @longMax
      AND latitude BETWEEN @latMin AND @latMax
      AND @categoryFilter LIKE '%' + subCategory + '%'
END

Answer 6

您可以尝试以下

ALTER PROCEDURE [dbo].[GetLocations]
    @longMax float =100,
    @longMin float=0,
    @latMax float=100,
    @latMin float=0,
    @categoryFilter char(200) = '',
    @Query varchar(2000)
AS
BEGIN
    SET NOCOUNT ON;
    Set @Query = 'SELECT *
            FROM dbo.Locations
           WHERE longitude BETWEEN @longMin AND @longMax
                  AND latitude BETWEEN @latMin AND @latMax
                  AND @categoryFilter IN (' + LTRIM(RTRIM(@categoryFilter)) + ')' 
    Exec(@Query)
END