SQL：存储过程中的in子句：如何传递值
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/1525126/
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 : in clause in stored procedure:how to pass values
提问by Shyju
I want to write a SQL Server 2005 stored procedure which will select and return the user records from the user table for some userids which are passed to the stored procedure as parameter.
我想编写一个 SQL Server 2005 存储过程，它将从用户表中选择并返回用户记录，以获取一些作为参数传递给存储过程的用户 ID。
How to do this ?
这该怎么做 ？
I can pass the user ids as a string separated by comma. So that I can use the
我可以将用户 ID 作为以逗号分隔的字符串传递。这样我就可以使用
select *
from users
where userid in (userids)
E.g. : I want to select records for id's 5,6,7,8,9
例如：我想为 id 的 5、6、7、8、9 选择记录
How to write the stored procedure ?
存储过程怎么写？
回答by marc_s
For SQL Server 2005, check out Erland Sommarskog's excellent Arrays and Lists in SQL Server 2005article which shows some techniques how to deal with lists and arrays in SQL Server 2005 (he also has another article for SQL Server 2000).
对于 SQL Server 2005，请查看 Erland Sommarskog 出色的SQL Server 2005 中的数组和列表文章，该文章展示了如何在 SQL Server 2005 中处理列表和数组的一些技术（他还有另一篇关于 SQL Server 2000 的文章）。
If you could upgrade to SQL Server 2008, you can use the new feature called "table valued parameter":
如果您可以升级到 SQL Server 2008，您可以使用名为“表值参数”的新功能：
First, create a user-defined table type
一、创建用户自定义表类型
CREATE TYPE dbo.MyUserIDs AS TABLE (UserID INT NOT NULL)
Secondly, use that table type in your stored procedure as a parameter:
其次，在您的存储过程中使用该表类型作为参数：
CREATE PROC proc_GetUsers @UserIDTable MyUserIDs READONLY
AS
SELECT * FROM dbo.Users
WHERE userid IN (SELECT UserID FROM @UserIDTable)
See details here.
在此处查看详细信息。
Marc
马克
回答by Shreyas Maratha
Just use it like this will work
像这样使用它会起作用
Create procedure sp_DoctorList
@userid varchar(100)
as
begin
exec ('select * from doctor where userid in ( '+ @userid +' )')
end
回答by Preet Sangha
you could use dynamic sql. Pass the in statement to a Sql SP via a variable and concatenate it into a query in the SQL and execute using sp_execute sql
你可以使用动态sql。通过变量将 in 语句传递给 Sql SP 并将其连接到 SQL 中的查询中并使用 sp_execute sql 执行
create procedure myproc(@clause varchar(100)) as
begin
exec sp_executesql 'select * from users where userid in ( ' + @clause +' )'
end
回答by KM.
see my previous answer to this
请参阅我之前对此的回答
this is the best source:
这是最好的来源：
http://www.sommarskog.se/arrays-in-sql.html
http://www.sommarskog.se/arrays-in-sql.html
create a split function, and use it like:
创建一个拆分函数，并像这样使用它：
SELECT
*
FROM YourTable y
INNER JOIN dbo.splitFunction(@Parameter) s ON y.ID=s.Value
I prefer the number table approach
For this method to work, you need to do this one time table setup:
要使此方法起作用，您需要执行此一次性表设置：
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 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 字符串拆分为一个表并在其上加入：
select * from dbo.FN_ListToTable(',','1,2,3,,,4,5,6777,,,')
OUTPUT:
输出：
ListValue
-----------------------
1
2
3
4
5
6777
(6 row(s) affected)
Your can pass in a CSV string into a procedure and process only rows for the given IDs:
您可以将 CSV 字符串传递到过程中，并仅处理给定 ID 的行：
SELECT
y.*
FROM YourTable y
INNER JOIN dbo.FN_ListToTable(',',@GivenCSV) s ON y.ID=s.ListValue
回答by Alex Bagnolini
Assuming T-SQL, you can use this nice function (that returns a table).
假设使用 T-SQL，您可以使用这个不错的函数（返回一个表）。
DROP FUNCTION sp_ConvertStringToTable
GO
CREATE FUNCTION sp_ConvertStringToTable(@list ntext)
RETURNS @tbl TABLE (Position INT IDENTITY(1, 1) NOT NULL,
Value INT NOT NULL) AS
BEGIN
DECLARE @pos int,
@textpos int,
@chunklen smallint,
@str nvarchar(4000),
@tmpstr nvarchar(4000),
@leftover nvarchar(4000)
SET @textpos = 1
SET @leftover = ''
WHILE @textpos <= datalength(@list) / 2
BEGIN
SET @chunklen = 4000 - datalength(@leftover) / 2
SET @tmpstr = ltrim(@leftover + substring(@list, @textpos, @chunklen))
SET @textpos = @textpos + @chunklen
SET @pos = charindex(' ', @tmpstr)
WHILE @pos > 0
BEGIN
SET @str = substring(@tmpstr, 1, @pos - 1)
INSERT @tbl (Value) VALUES(convert(int, @str))
SET @tmpstr = ltrim(substring(@tmpstr, @pos + 1, len(@tmpstr)))
SET @pos = charindex(' ', @tmpstr)
END
SET @leftover = @tmpstr
END
IF ltrim(rtrim(@leftover)) <> ''
INSERT @tbl (Value) VALUES(convert(int, @leftover))
RETURN
END
GO
In this way:
通过这种方式：
SELECT * FROM Users
WHERE userid IN
( SELECT Value FROM sp_ConvertStringToTable('1 2 3') )
You can change the stored function to work with comma separated strings instead of space separated ones.
您可以更改存储的函数以使用逗号分隔的字符串而不是空格分隔的字符串。
If you don't want / can't use a stored function you can include the code of it inside the stored procedure where needed.
如果您不想/不能使用存储函数，您可以在需要的地方将其代码包含在存储过程中。
EDIT: this is incredibly more performant than the string concatenation.
编辑：这比字符串连接的性能要好得多。
回答by Tanmay Nehete
try this this works for me
试试这个对我有用
DECLARE @InClause NVARCHAR(100)
SET @InClause = 'tom,dick,harry'
DECLARE @SafeInClause NVARCHAR(100)
SET @SafeInClause = ',' + @InClause + ','
SELECT * FROM myTable WHERE PATINDEX(',' + myColumn + ',', @SafeInClause) > 0
回答by yakult
Quick and dirty..
又快又脏。。
CREATE PROCEDURE SelectUsers (@UserIds VARCHAR(8000))
AS
SELECT * FROM Users
WHERE userid IN (SELECT CONVERT(VARCHAR(8000), value) FROM STRING_SPLIT(@UserIds, ','))
EXEC SelectUsers @UserIds = 'a1b2,c3d4,e5f6'
回答by bala-16
You can also use Find_IN_SET instead of IN. See the query below
您也可以使用 Find_IN_SET 而不是 IN。请参阅下面的查询
create procedure myproc(IN in_user_ids varchar(100))
begin
select * from users where FIND_IN_SET(userid, in_user_ids);
end
