如何在存储过程中将一列中的多个值与定界字符串进行比较 [英] How to compare multiple values in one column against a delimited string in a stored procedure

问题描述

好吧，第一个问题，你们用我知道的多少吓到我了，所以请保持温柔...

Ok, first question, and you guys scare me with how much you know, so please be gentle...

我正在尝试传递定界字符串并将其转换为存储过程中的数组，然后使用该数组检查列中的值。这里是要抓住的地方。我正在尝试使用一个预先存在的表，该表检查一个关联并将其扩展为允许多个关联。

I am trying to pass in a delimited string and convert it to an array in a stored procedure and then use the array to check against values in a column. Here's the catch. I'm trying to take the preexisting table, that checks for one association and expand it to allow for multiple associations.

因此，annAssociations列可能具有三个ID，即4 16,32，但我需要检查它是否属于正在查询的6、12、32的groupId。由于其中一个值匹配，因此应返回该行。

So the column annAssociations might have three ids, 4,16,32, but I need to check if it belongs to the groupIds being queried, 6,12,32. Since one of the values matched, it should return that row.

这里是存在的过程。

CREATE PROCEDURE [dbo].[sp_annList] 
-- Date Range of Announcements.
@dateBegin datetime,
@dateEnd datetime,
    -- Announcement type and associations.
@annType varchar(50),
@annAssociation varchar(255)

AS
BEGIN

-- Set the SELECT statement for the announcements.
SET NOCOUNT ON;
-- See if this should be a limited query
IF @annAssociation <> ''
    Begin
        SELECT * 
        FROM announcements
        WHERE (date1 <= @dateEnd AND date2 >= @dateBegin) -- See if the announcement falls in the date range.
            AND annType = @annType -- See if the announcement is the right type.
            AND annAssociations LIKE (select SplitText from dbo.fnSplit(@annAssociation, ','))
        ORDER BY title
    END
Else 
    Begin
        SELECT *
        FROM announcements
        WHERE (date1 <= @dateEnd AND date2 >= @dateBegin)
            AND annType = @annType
        ORDER BY title
    End
END

这是我用来转换定界字符串并将其存储在其中的方法

And here is the method I'm using to convert the delimited string and store it in a temporary table.

CREATE Function [dbo].[fnSplit](@text text, @delimitor nchar(1))

RETURNS
@table TABLE
(
[Index] int Identity(0,1),
[SplitText] varchar(10)
)
AS

BEGIN
declare @current varchar(10)
declare @endIndex int
declare @textlength int
declare @startIndex int

set @startIndex = 1

if(@text is not null)
begin
    set @textLength = datalength(@text)

    while(1=1)
    begin
        set @endIndex = charindex(@delimitor, @text, @startIndex)

        if(@endIndex != 0)
        begin
            set @current = substring(@text,@startIndex, @endIndex - @StartIndex)
            Insert Into @table ([SplitText]) values(@current)
            set @startIndex = @endIndex + 1   
        end
        else
        begin
            set @current = substring(@text, @startIndex, datalength(@text)-@startIndex+1)
            Insert Into @table ([SplitText]) values(@current)
            break
        end
    end

end

return
END

很抱歉，冗长的问题。我只是想把所有信息都拿到那里。我已经研究了好几天了，或者我不知道在哪里寻找或缺少明显的东西。

Sorry for the long question. I just wanted to get all the info out there. I've been researching for days, and I either don't know where to look or am missing something obvious.

推荐答案

您可能不会比这种方法获得更好的性能（好吧，使用CLR拆分功能可能会看到更好的性能，但是在3到4个项目中，您不会看到太大的区别）：

You probably won't get much better performance than this approach (well you might see better performance with a CLR split function but at 3 or 4 items you won't see much difference):

SELECT * 
  FROM announcements AS a
  WHERE ...
  AND EXISTS (SELECT 1 FROM dbo.fnSplit(@annAssociation) AS n
    WHERE ',' + a.annList + ',' LIKE '%,' + n.SplitText + ',%');

这里的关键是您只需要拆分一个列表即可。

The key here is that you only need to split up one of the lists.

您确实应该停止在 annAssocations 列中存储多个值。每个id是一条单独的数据，应该分别存储（除了更好地符合规范之外，它还会使查询变得更简单）。

You really should stop storing multiple values in the annAssocations column. Each id is a separate piece of data and should be stored separately (in addition to better conforming to normalization, it will make queries like this simpler).

这篇关于如何在存储过程中将一列中的多个值与定界字符串进行比较的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！