SQL - 使用 IN 的带有 Select 语句的存储过程 (@Variable_CommaDelimitedListOfIDS)
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/2584977/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
SQL - Stored Procedure with Select Statement using IN (@Variable_CommaDelimitedListOfIDS)
提问by GigaPr
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 列表作为变量传递给它。我想将 Id 用于 select 语句，例如：
Create Procedure up_TEST
@Ids VARCHAR(MAX)
AS
SELECT * FROM ATable a
WHERE a.Id IN(@Ids)
Obviously I get the error that @Idsis a varchar and not an INT but how can I convert the comma delimited list?
显然我得到的错误@Ids是 varchar 而不是 INT 但如何转换逗号分隔列表？
采纳答案by Jhonny D. Cano -Leftware-
For those cases I use this table function, which you can adapt to your needs:
对于这些情况，我使用这个 table 函数，您可以根据自己的需要进行调整：
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))
回答by TGnat
Since you're using SQL Server 2008, have a look at table-valued parameters.
由于您使用的是 SQL Server 2008，请查看表值参数。
回答by KM.
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 中有多种拆分字符串的方法。本文涵盖了几乎每种方法的优点和缺点：
“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 TSQLbut 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 中有多种拆分字符串的方法，请参阅上一个链接，其中解释了每种方法的优点和缺点。
For the Numbers Table method to work, you need to do this one time table setup, which will create a table Numbersthat contains rows from 1 to 10,000:
要使 Numbers Table 方法起作用，您需要执行此一次性表设置，这将创建一个Numbers包含 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))
回答by eftpotrm
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- 这是一种处理分隔输入变量的方法，通过加入反对来提供包含拆分项目的结果集一个数字/计数表（如果您还没有，那么在数据库中拥有它是一件好事）。这将为您提供一个简单的结果集，您可以按照正常方式加入该结果集。
回答by SHOBHIT SAXENA
CREATE PROCEDURE PROCEDURENAME
@Id Numeric(18,0)
AS
BEGIN
SELECT * FROM tableName
WHERE tableName.Id IN (@Id)
END
回答by Amy B
If you're having this problem, you should read Sommarskog's articles.
如果您遇到此问题，您应该阅读 Sommarskog 的文章。
http://www.sommarskog.se/index.html
http://www.sommarskog.se/index.html
I recommend:
我建议：
- Arrays and Lists in Sql Server
- The curse and blessings of dynamic SQL.
- Sql Server 中的数组和列表
- 动态SQL的诅咒与祝福。
回答by Emrah GOZCU
One way could be split and insert the ids into a tempory table and than use SQL IN clause...
一种方法可以拆分并将 id 插入到临时表中，而不是使用 SQL IN 子句...
