将充满逗号分隔值的 varchar 传递给 SQL Server IN 函数
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/878833/
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
Passing a varchar full of comma delimited values to a SQL Server IN function
提问by Vance Smith
Duplicate of
Dynamic SQL Comma Delimited Value Query
Parameterized Queries with Like and In
I have a SQL Server Stored Procedure where I would like to pass a varcharfull of comma delimited values to an INfunction. For example:
我有一个 SQL Server 存储过程，我想在其中将varchar完整的逗号分隔值传递给IN函数。例如：
DECLARE @Ids varchar(50);
SET @Ids = '1,2,3,5,4,6,7,98,234';
SELECT *
FROM sometable
WHERE tableid IN (@Ids);
This does not work of course. I get the error:
这当然行不通。我收到错误：
Conversion failed when converting the varchar value '1,2,3,5,4,6,7,98,234' to data type int.
将 varchar 值 '1,2,3,5,4,6,7,98,234' 转换为数据类型 int 时转换失败。
How can I accomplish this (or something relatively similar) without resorting to building dynamic SQL?
如何在不求助于构建动态 SQL 的情况下完成此操作（或相对类似的操作）？
采纳答案by KM.
Don't use a function that loops to split a string!, my function below will split a string very fast, with no looping!
不要使用循环函数来分割字符串！，我下面的函数将非常快速地分割一个字符串，没有循环！
Before you use my function, you need to set up a "helper" table, you only need to do this one time per database:
在您使用我的功能之前，您需要设置一个“助手”表，您只需为每个数据库执行一次：
CREATE TABLE Numbers
(Number int NOT NULL,
CONSTRAINT PK_Numbers PRIMARY KEY CLUSTERED (Number ASC)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
DECLARE @x int
SET @x=0
WHILE @x<8000
BEGIN
SET @x=@x+1
INSERT INTO Numbers VALUES (@x)
END
use this function to split your string, which does not loop and is very fast:
使用这个函数来分割你的字符串，它不会循环并且非常快：
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
@ParsedList table
(
ListValue varchar(500)
)
AS
BEGIN
/**
Takes the given @List string and splits it apart based on the given @SplitOn character.
A table is returned, one row per split item, with a column name "ListValue".
This function workes for fixed or variable lenght items.
Empty and null items will not be included in the results set.
Returns a table, one row per item in the list, with a column name "ListValue"
EXAMPLE:
----------
SELECT * FROM dbo.FN_ListToTable(',','1,12,123,1234,54321,6,A,*,|||,,,,B')
returns:
ListValue
-----------
1
12
123
1234
54321
6
A
*
|||
B
(10 row(s) affected)
**/
----------------
--SINGLE QUERY-- --this will not return empty rows
----------------
INSERT INTO @ParsedList
(ListValue)
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!=''
RETURN
END --Function FN_ListToTable
you can use this function as a table in a join:
您可以将此函数用作连接中的表：
SELECT
Col1, COl2, Col3...
FROM YourTable
INNER JOIN FN_ListToTable(',',@YourString) s ON YourTable.ID = s.ListValue
Here is your example:
这是你的例子：
Select * from sometable where tableid in(SELECT ListValue FROM dbo.FN_ListToTable(',',@Ids) s)
回答by RBarryYoung
Of course if you're lazy like me, you could just do this:
当然，如果你像我一样懒惰，你可以这样做：
Declare @Ids varchar(50) Set @Ids = ',1,2,3,5,4,6,7,98,234,'
Select * from sometable
where Charindex(','+cast(tableid as varchar(8000))+',', @Ids) > 0
回答by CeejeeB
No Table No Function No Loop
无表 无函数 无循环
Building on the idea of parsing your list into a table our DBA suggested using XML.
基于将您的列表解析为表的想法，我们的 DBA 建议使用 XML。
Declare @Ids varchar(50)
Set @Ids = ‘1,2,3,5,4,6,7,98,234'
DECLARE @XML XML
SET @XML = CAST('<i>' + REPLACE(@Ids, ',', '</i><i>') + '</i>' AS XML)
SELECT *
FROM
SomeTable
INNER JOIN @XML.nodes('i') x(i)
ON SomeTable .Id = x.i.value('.', 'VARCHAR(MAX)')
These seems to have the same performance as @KM's answer but, I think, a lot simpler.
这些似乎与@KM 的答案具有相同的性能，但我认为要简单得多。
回答by Richard L
You can create a function that returns a table.
您可以创建一个返回表的函数。
so your statement would be something like
所以你的陈述会是这样的
select * from someable
join Splitfunction(@ids) as splits on sometable.id = splits.id
Here is a simular function.
这是一个模拟函数。
CREATE FUNCTION [dbo].[FUNC_SplitOrderIDs]
(
@OrderList varchar(500)
)
RETURNS
@ParsedList table
(
OrderID int
)
AS
BEGIN
DECLARE @OrderID varchar(10), @Pos int
SET @OrderList = LTRIM(RTRIM(@OrderList))+ ','
SET @Pos = CHARINDEX(',', @OrderList, 1)
IF REPLACE(@OrderList, ',', '') <> ''
BEGIN
WHILE @Pos > 0
BEGIN
SET @OrderID = LTRIM(RTRIM(LEFT(@OrderList, @Pos - 1)))
IF @OrderID <> ''
BEGIN
INSERT INTO @ParsedList (OrderID)
VALUES (CAST(@OrderID AS int)) --Use Appropriate conversion
END
SET @OrderList = RIGHT(@OrderList, LEN(@OrderList) - @Pos)
SET @Pos = CHARINDEX(',', @OrderList, 1)
END
END
RETURN
END
回答by A-K
It's a very common question. Canned answer, several nice techniques:
这是一个很常见的问题。罐头答案，几个不错的技巧：
回答by Eric
This works perfectly! The below answers are too complicated. Don't look at this as dynamic. Set up your store procedure as follows:
这工作得很好！下面的答案太复杂了。不要将其视为动态的。设置您的存储过程如下：
(@id as varchar(50))
as
Declare @query as nvarchar(max)
set @query ='
select * from table
where id in('+@id+')'
EXECUTE sp_executesql @query
回答by HLGEM
Without using dynamic SQL, you have to take the input variable and use a split function to put the data into a temp table and then join to that.
在不使用动态 SQL 的情况下，您必须获取输入变量并使用拆分函数将数据放入临时表中，然后加入该表。
回答by shA.t
I can suggest using WITHlike this:
我可以建议这样使用WITH：
DECLARE @Delim char(1) = ',';
SET @Ids = @Ids + @Delim;
WITH CTE(i, ls, id) AS (
SELECT 1, CHARINDEX(@Delim, @Ids, 1), SUBSTRING(@Ids, 1, CHARINDEX(@Delim, @Ids, 1) - 1)
UNION ALL
SELECT i + 1, CHARINDEX(@Delim, @Ids, ls + 1), SUBSTRING(@Ids, ls + 1, CHARINDEX(@Delim, @Ids, ls + 1) - CHARINDEX(@Delim, @Ids, ls) - 1)
FROM CTE
WHERE CHARINDEX(@Delim, @Ids, ls + 1) > 1
)
SELECT t.*
FROM yourTable t
INNER JOIN
CTE c
ON t.id = c.id;
回答by Shakti Singh Chauhan
Create a table function like below which parse comma separated varchar and returns a table that can be inner joined with other tables.
创建一个表函数，如下所示，它解析逗号分隔的 varchar 并返回一个可以与其他表内部连接的表。
CREATE FUNCTION [dbo].[fn_SplitList]
(
@inString varchar(MAX) = '',
@inDelimiter char(1) = ',' -- Keep the delimiter to 100 chars or less. Generally a delimiter will be 1-2 chars only.
)
RETURNS @tbl_Return table
(
Unit varchar(1000) COLLATE Latin1_General_BIN
)
AS
BEGIN
INSERT INTO @tbl_Return
SELECT DISTINCT
LTRIM(RTRIM(piece.value('./text()[1]', 'varchar(1000)'))) COLLATE DATABASE_DEFAULT AS Unit
FROM
(
--
-- Replace any delimiters in the string with the "X" tag.
--
SELECT
CAST(('<X>' + REPLACE(s0.prsString, s0.prsSplitDelimit, '</X><X>') + '</X>') AS xml).query('.') AS units
FROM
(
--
-- Convert the string and delimiter into XML.
--
SELECT
(SELECT @inString FOR XML PATH('')) AS prsString,
(SELECT @inDelimiter FOR XML PATH('')) AS prsSplitDelimit
) AS s0
) AS s1
CROSS APPLY units.nodes('X') x(piece)
RETURN
END
================================================= Now consume above created table function in your code,creation of function is one time activity in your database that can be used across databases as well on same server.
================================================== 现在在代码中使用上面创建的表函数，函数的创建是数据库中的一次活动，可以跨数据库使用，也可以在同一服务器上使用。
DECLARE @Ids varchar(50);
SET @Ids = '1,2,3,5,4,6,7,98,234';
SELECT
*
FROM sometable AS st
INNER JOIN fn_SplitList(@ids, ',') AS sl
ON sl.unit = st.tableid
回答by BornToCode
If you use SQL Server 2008 or higher, use table valued parameters; for example:
如果您使用 SQL Server 2008 或更高版本，请使用表值参数；例如：
CREATE PROCEDURE [dbo].[GetAccounts](@accountIds nvarchar)
AS
BEGIN
SELECT *
FROM accountsTable
WHERE accountId IN (select * from @accountIds)
END
CREATE TYPE intListTableType AS TABLE (n int NOT NULL)
DECLARE @tvp intListTableType
-- inserts each id to one row in the tvp table
INSERT @tvp(n) VALUES (16509),(16685),(46173),(42925),(46167),(5511)
EXEC GetAccounts @tvp
