SQL - 使用IN的Select语句存储过程（@Variable_CommaDelimitedListOfIDS）

I am creating a stored procedure to which I want to pass as variable a comma delimited list of Ids. I want to use the Ids into a select statement, something like:

我正在创建一个存储过程，我想将其作为变量传递给逗号分隔的ID列表。我想将Ids用于select语句，例如：

Create Procedure up_TEST
@Ids VARCHAR(MAX)
AS
SELECT * FROM ATable a
WHERE a.Id IN(@Ids)

Obviously I get the error that @Ids is a varchar and not an INT but how can I convert the comma delimited list?

显然我得到的错误是@Ids是varchar而不是INT，但是如何转换逗号分隔列表呢？

I am using SQL Server 2008

我正在使用SQL Server 2008

Thanks

谢谢

7 个解决方案

#1

For those cases I use this table function, which you can adapt to your needs:

对于这些情况，我使用此表函数，您可以根据自己的需要进行调整：

CREATE FUNCTION dbo.f_params_to_list (@par VARCHAR(500))
returns @result TABLE (value VARCHAR(30))
AS  
begin
     DECLARE @TempList table
          (
          value VARCHAR(30)
          )

     DECLARE @Value varchar(30), @Pos int

     SET @par = LTRIM(RTRIM(@par))+ ','
     SET @Pos = CHARINDEX(',', @par, 1)

     IF REPLACE(@par, ',', '') <> ''
     BEGIN
          WHILE @Pos > 0
          BEGIN
               SET @Value = LTRIM(RTRIM(LEFT(@par, @Pos - 1)))
               IF @Value <> ''
               BEGIN
                    INSERT INTO @TempList (value) VALUES (@Value) --Use Appropriate conversion
               END
               SET @par = RIGHT(@par, LEN(@par) - @Pos)
               SET @Pos = CHARINDEX(',', @par, 1)

          END
     END    
     INSERT @result
     SELECT value 
        FROM @TempList
     RETURN
END

In your stored procedure you would use it like this:

在您的存储过程中，您将使用它像这样：

Create Procedure up_TEST
@Ids VARCHAR(MAX)
AS
SELECT * FROM ATable a
WHERE a.Id IN(SELECT value FROM dbo.f_params_to_list(@Ids))

#2

Since you're using SQL Server 2008, have a look at table-valued parameters.

由于您使用的是SQL Server 2008，因此请查看表值参数。

#3

Use a table values parameter (new in SQl Server 2008). Set it up by creating the actual table parameter type:

使用表值参数（SQl Server 2008中的新增功能）。通过创建实际的表参数类型来设置它：

CREATE TYPE IntTableType AS TABLE (ID INTEGER PRIMARY KEY)

Your procedure would then be:

那么你的程序是：

Create Procedure up_TEST
    @Ids IntTableType READONLY
AS

SELECT * 
    FROM ATable a
    WHERE a.Id IN (SELECT ID FROM @Ids)

RETURN 0
GO

if you can't use table value parameters, see: "Arrays and Lists in SQL Server 2008 Using Table-Valued Parameters" by Erland Sommarskog , then there are many ways to split string in SQL Server. This article covers the PROs and CONs of just about every method:

如果你不能使用表值参数，请参阅：Erland Sommarskog的“使用表值参数的SQL Server 2008中的数组和列表”，那么有很多方法可以在SQL Server中拆分字符串。本文涵盖几乎所有方法的PRO和CON：

"Arrays and Lists in SQL Server 2005 and Beyond, When Table Value Parameters Do Not Cut it" by Erland Sommarskog

“表格值参数不会削减它时，SQL Server 2005及更高版本中的数组和列表”作者：Erland Sommarskog

You need to create a split function. This is how a split function can be used:

您需要创建拆分功能。这是分割函数的使用方式：

SELECT
    *
    FROM YourTable                               y
    INNER JOIN dbo.yourSplitFunction(@Parameter) s ON y.ID=s.Value

I prefer the number table approach to split a string in TSQL but there are numerous ways to split strings in SQL Server, see the previous link, which explains the PROs and CONs of each.

我更喜欢使用数字表方法在TSQL中拆分字符串，但是有很多方法可以在SQL Server中拆分字符串，请参阅前面的链接，它解释了每个链接的PRO和CON。

For the Numbers Table method to work, you need to do this one time table setup, which will create a table Numbers that contains rows from 1 to 10,000:

要使Numbers Table方法起作用，您需要执行一次性表设置，这将创建一个包含1到10,000行的表号：

SELECT TOP 10000 IDENTITY(int,1,1) AS Number
    INTO Numbers
    FROM sys.objects s1
    CROSS JOIN sys.objects s2
ALTER TABLE Numbers ADD CONSTRAINT PK_Numbers PRIMARY KEY CLUSTERED (Number)

Once the Numbers table is set up, create this split function:

设置Numbers表后，创建此拆分功能：

CREATE FUNCTION [dbo].[FN_ListToTable]
(
     @SplitOn  char(1)      --REQUIRED, the character to split the @List string on
    ,@List     varchar(8000)--REQUIRED, the list to split apart
)
RETURNS TABLE
AS
RETURN 
(

    ----------------
    --SINGLE QUERY-- --this will not return empty rows
    ----------------
    SELECT
        ListValue
        FROM (SELECT
                  LTRIM(RTRIM(SUBSTRING(List2, number+1, CHARINDEX(@SplitOn, List2, number+1)-number - 1))) AS ListValue
                  FROM (
                           SELECT @SplitOn + @List + @SplitOn AS List2
                       ) AS dt
                      INNER JOIN Numbers n ON n.Number < LEN(dt.List2)
                  WHERE SUBSTRING(List2, number, 1) = @SplitOn
             ) dt2
        WHERE ListValue IS NOT NULL AND ListValue!=''

);
GO

You can now easily split a CSV string into a table and join on it:

您现在可以轻松地将CSV字符串拆分为表格并加入其中：

Create Procedure up_TEST
@Ids VARCHAR(MAX)
AS
SELECT * FROM ATable a
WHERE a.Id IN (SELECT ListValue FROM dbo.FN_ListToTable(',',@Ids))

#4

If you're having this problem, you should read Sommarskog's articles.

如果你遇到这个问题，你应该阅读Sommarskog的文章。

http://www.sommarskog.se/index.html

I recommend:

我建议：

Arrays and Lists in Sql Server
Sql Server中的数组和列表
The curse and blessings of dynamic SQL.
动态SQL的诅咒和祝福。

#5

Everyone seems to be doing something like this today!

今天似乎每个人都在做这样的事情！

Have a look at http://www.sqlteam.com/article/parsing-csv-values-into-multiple-rows - this is a way of processing a delimited input variable to give a results set containing the split items by joining against a numbers / tally table (which is a good thing to have in the database anyway if you haven't yet). This then gives you a simple results set which you can join against as per normal.

看一下http://www.sqlteam.com/article/parsing-csv-values-into-multiple-rows - 这是一种处理分隔输入变量的方法，通过加入来提供包含拆分项的结果集一个数字/理货表（如果你还没有，那么在数据库中这是一件好事）。然后，这会为您提供一个简单的结果集，您可以按照正常情况加入。

#6

CREATE PROCEDURE PROCEDURENAME
@Id Numeric(18,0)
AS
BEGIN
    SELECT * FROM tableName
    WHERE tableName.Id IN (@Id)
END

#7

One way could be split and insert the ids into a tempory table and than use SQL IN clause...

可以拆分一种方法，将id插入临时表，然后使用SQL IN子句...

#1