将逗号分隔的参数传递给存储过程

引言

很多时候我都想将一个 `In` 子句传递给一个存储过程，以便使用一个包含多个值的参数来返回一个数据的子集。但是，正如你可能已经知道的，除非使用动态 SQL，否则这并不容易。如果它是一个已链接到可以过滤数据分组的列的单个值，那么就不是问题。

这里有一个例子。假设我们在东南部地区有很多销售员。显然，会有一个区域表将所有销售员链接到该区域。但是，如果我们只想知道选定的顶级销售员来自哪些城市呢？现在这变成了一个难题，特别是当前端使用存储过程从用户选定的销售员列表中检索城市列表时。当然，我们可以有一个存储过程，它返回来自一个办公室、州或区域的销售员列表。但是，在不打开查询分析器的情况下，根据用户选定的销售员列表返回城市列表可能会非常困难。难道不能简单地将销售员 ID 的逗号分隔列表传递给过程，以检索所有销售员及其各自的城市吗？

好的，答案就在这里。这个代码部分是从许多不同网站的各种帖子中获得的，所以我不会声称拥有所有功劳。如果碰巧一些代码是你的，只需给我发一封电子邮件，附上你的代码发布链接，我就会将你列入致谢名单。如果我对你的代码感到抱歉，我很抱歉。但是，我认为这对许多开发者来说将非常有帮助，因为它对我自己来说也很有用。

实现代码

步骤 1

我们需要在数据库中创建一个永久表来跟踪计数。它是一个简单的单列表，需要用递增的整数来填充。我称我的表为 `tblToolsStringParserCounter`，并用 1 到 1000 的值填充行。如果需要，你可以增加更多。但我不确定性能。我还没有需要传递超过大约六个逗号分隔值的情况。

好的，让我们先打开 SQL 查询分析器。我们首先需要运行以下代码来创建测试数据库并执行 USE 语句。

/* Script to Create Database */
CREATE DATABASE TestParserLogic
GO
USE TestParserLogic
GO

现在让我们创建解析器计数表。这个表将用于解析发送到存储过程的逗号分隔参数。

/* Script to create the table */
CREATE TABLE tblToolsStringParserCounter
(
    ID INT
)

我们仍然需要填充该表。除非表从数据库中删除，否则只需要执行一次。我使用一个从 1 到 1000 的循环，并在循环期间插入每个值。

/* Populate the table */
DECLARE @i INT
SELECT    @i = 1

WHILE (@i <= 1000)
BEGIN
    INSERT INTO tblToolsStringParserCounter SELECT @i
    SELECT @i = @i + 1
END
GO

第二步

现在表已经创建，让我们演示一下它的实用性。我将不对下面的查询进行详细分析。但是，它的职责是借助上面新创建的表来解析逗号分隔的字符串。

这是将逗号分隔的字符串解析为行的查询

SELECT  Convert(Int, NullIf(SubString(',' + @IDs + ',' , 
  ID , CharIndex(',' , ',' + @IDs + ',' , ID) - ID) , '')) AS IDList 
FROM    tblToolsStringParserCounter
WHERE   ID <= Len(',' + @IDs + ',') AND 
  SubString(',' + @IDs + ',' , ID - 1, 1) = ',' 
AND     CharIndex(',' , ',' + @IDs + ',' , ID) - ID > 0 

该语句的结果类似于执行单列 select 语句的输出。现在，让我们尝试运行该查询。

DECLARE @IDs varchar(100)
SELECT  @IDs = '429,446,552,1001, 332 , 471' 
    --Any IDs as an example

SELECT  Convert(Int, NullIf(SubString(',' + @IDs + 
  ',' , ID , CharIndex(',' , ',' + @IDs + ',' , ID) - 
  ID) , '')) AS IDList 
FROM    tblToolsStringParserCounter
WHERE   ID <= Len(',' + @IDs + ',') AND SubString(',' + 
  @IDs + ',' , ID - 1, 1) = ',' 
AND     CharIndex(',' , ',' + @IDs + ',' , ID) - ID > 0  

这是上述 SQL 脚本的输出。

IDList      
----------- 
429
446
552
1001
332
471

请注意，SQL 语句对于 `@IDs` 变量中的空格和缺失的结束逗号非常宽容。这对需要为前端开发人员设计存储过程的 SQL 开发人员来说非常棒。

步骤 3

现在，让我们通过创建 `salesman` 和 `cities` 表并填充数据来创建一些测试数据。

CREATE TABLE tblCity
(    CityID Int IDENTITY (1, 1) NOT NULL,
  City varchar(12) NOT NULL
)
GO
INSERT INTO tblCity (City) VALUES ('Houston')
INSERT INTO tblCity (City) VALUES ('New Orleans')
INSERT INTO tblCity (City) VALUES ('Atlanta')
INSERT INTO tblCity (City) VALUES ('Orlando')

CREATE TABLE tblSalesman
(    SalesmanID Int IDENTITY (1, 1) NOT NULL,
  SalesmanName varchar(10) NOT NULL,
    CityID Int NOT NULL,
)
GO
INSERT INTO tblSalesman (SalesmanName, CityID) VALUES ('George', 1)
INSERT INTO tblSalesman (SalesmanName, CityID) VALUES ('Mark', 2)
INSERT INTO tblSalesman (SalesmanName, CityID) VALUES ('Greg', 3)
INSERT INTO tblSalesman (SalesmanName, CityID) VALUES ('Susie', 4)
INSERT INTO tblSalesman (SalesmanName, CityID) VALUES ('Kevin', 3)
INSERT INTO tblSalesman (SalesmanName, CityID) VALUES ('Bobby', 1)
INSERT INTO tblSalesman (SalesmanName, CityID) VALUES ('Terry', 1)
INSERT INTO tblSalesman (SalesmanName, CityID) VALUES ('Betty', 2)
INSERT INTO tblSalesman (SalesmanName, CityID) VALUES ('Carl', 2)
INSERT INTO tblSalesman (SalesmanName, CityID) VALUES ('Gary', 4)

验证新创建的表和数据的结果。

使用简单的 select 语句查询数据。

SELECT  SalesmanName, City 
FROM    tblSalesman s,
        tblCity c
WHERE   s.CityID = c.CityID

上述查询的结果。

SalesmanName City         
------------ ------------ 
George       Houston
Mark         New Orleans
Greg         Atlanta
Susie        Orlando
Kevin        Miami
Bobby        Houston
Terry        Houston
Betty        New Orleans
Carl         New Orleans
Gary         Orlando

步骤 4

现在我们需要创建一个存储过程，从逗号分隔的 `SalesmanID` 列表中查询所有城市。在过程中，我们将需要创建一个临时表 `#1` 来保存传递给过程的 ID。

请记住，用户希望知道他们选择的每个销售员来自哪些城市。

CREATE PROCEDURE sp_CityBySalesman
(
    @IDs as varchar(100) --SalesmanIDs
)
AS

    CREATE TABLE #1
    (
        IDList Int
    )
    -- Optional index on the temp table
    CREATE INDEX idx1 ON #1 (IDList)

    INSERT INTO #1
    SELECT  Convert(Int, NullIf(SubString(',' + @IDs + ',' , ID ,
     CharIndex(',' , ',' + @IDs + ',' , ID) - ID) , '')) AS IDList 
    FROM    tblToolsStringParserCounter
    WHERE   ID <= Len(',' + @IDs + ',') AND SubString(',' + 
      @IDs + ',' , ID - 1, 1) = ',' 
    AND     CharIndex(',' , ',' + @IDs + ',' , ID) - ID > 0  

    /* 
       Now use the temp table, #1 containing the list of SalesmanIDs 
       passed by the parameter @IDs, to obtain all of the Salesman's 
       cities respectively in the final select statement of the procedure

    */

    SELECT  SalesmanName, City
    FROM    tblSalesman s,
            #1 t,
            tblCity c
    WHERE   s.CityID = c.CityID
    AND     t.IDList = s.SalesmanID

GO

步骤 5

现在让我们执行存储过程并查看结果。

sp_SalesmanByCity '1,3,5, 6 ,9 '

请注意，传递的参数包含空格和缺失的结束逗号。

结果

SalesmanName City         
------------ ------------ 
George       Houston
Bobby        Houston
Carl         New Orleans
Greg         Atlanta
Kevin        Miami

关注点

我使用 ASP.Net、VB.Net 和 SQL Server 实现的代码编写了一个应用程序。用户有权同时查看多个办公室的数据。由于用户权限是缓存的，因此这似乎是实现应用程序业务规则的最简单方法。

这是我发布在 The Code Project 上的第一篇文章。我希望它有所帮助且信息丰富。我的下一篇潜在文章将是关于 SQL Server 中的交叉表查询。