SQL 将参数数组传递给存储过程
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/1069311/
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 an array of parameters to a stored procedure
提问by markiz
I need to pass an array of "id's" to a stored procedure, to delete all rows from the table EXCEPT the rows that match id's in the array.
我需要将一个“id”数组传递给一个存储过程，以从表中删除所有行，除了与数组中的 id 匹配的行。
How can I do it in a most simple way?
我怎样才能以最简单的方式做到这一点？
采纳答案by Zanoni
Use a stored procedure:
使用存储过程：
EDIT:A complement for serialize List (or anything else):
编辑：序列化列表（或其他任何东西）的补充：
List<string> testList = new List<int>();
testList.Add(1);
testList.Add(2);
testList.Add(3);
XmlSerializer xs = new XmlSerializer(typeof(List<int>));
MemoryStream ms = new MemoryStream();
xs.Serialize(ms, testList);
string resultXML = UTF8Encoding.UTF8.GetString(ms.ToArray());
The result (ready to use with XML parameter):
结果（准备好与 XML 参数一起使用）：
<?xml version="1.0"?>
<ArrayOfInt xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema">
<int>1</int>
<int>2</int>
<int>3</int>
</ArrayOfInt>
ORIGINAL POST:
原帖：
Passing XML as parameter:
将 XML 作为参数传递：
<ids>
<id>1</id>
<id>2</id>
</ids>
CREATE PROCEDURE [dbo].[DeleteAllData]
(
@XMLDoc XML
)
AS
BEGIN
DECLARE @handle INT
EXEC sp_xml_preparedocument @handle OUTPUT, @XMLDoc
DELETE FROM
YOURTABLE
WHERE
YOUR_ID_COLUMN NOT IN (
SELECT * FROM OPENXML (@handle, '/ids/id') WITH (id INT '.')
)
EXEC sp_xml_removedocument @handle
回答by rmiesen
If you are using Sql Server 2008 or better, you can use something called a Table-Valued Parameter (TVP) instead of serializing & deserializing your list data every time you want to pass it to a stored procedure.
如果您使用的是 Sql Server 2008 或更高版本，则可以使用称为表值参数 (TVP) 的东西，而不是每次要将列表数据传递给存储过程时都对其进行序列化和反序列化。
Let's start by creating a simple schema to serve as our playground:
让我们首先创建一个简单的模式作为我们的操场：
CREATE DATABASE [TestbedDb]
GO
USE [TestbedDb]
GO
/* First, setup the sample program's account & credentials*/
CREATE LOGIN [testbedUser] WITH PASSWORD=N'μ×?
?S[°?Q-￥?q?_???D)3???%dv', DEFAULT_DATABASE=[master], DEFAULT_LANGUAGE=[us_english], CHECK_EXPIRATION=OFF, CHECK_POLICY=ON
GO
CREATE USER [testbedUser] FOR LOGIN [testbedUser] WITH DEFAULT_SCHEMA=[dbo]
GO
EXEC sp_addrolemember N'db_owner', N'testbedUser'
GO
/* Now setup the schema */
CREATE TABLE dbo.Table1 ( t1Id INT NOT NULL PRIMARY KEY );
GO
INSERT INTO dbo.Table1 (t1Id)
VALUES
(1),
(2),
(3),
(4),
(5),
(6),
(7),
(8),
(9),
(10);
GO
With our schema and sample data in place, we are now ready to create our TVP stored procedure:
有了我们的架构和示例数据，我们现在可以创建我们的 TVP 存储过程：
CREATE TYPE T1Ids AS Table (
t1Id INT
);
GO
CREATE PROCEDURE dbo.FindMatchingRowsInTable1( @Table1Ids AS T1Ids READONLY )
AS
BEGIN
SET NOCOUNT ON;
SELECT Table1.t1Id FROM dbo.Table1 AS Table1
JOIN @Table1Ids AS paramTable1Ids ON Table1.t1Id = paramTable1Ids.t1Id;
END
GO
With both our schema and API in place, we can call the TVP stored procedure from our program like so:
有了我们的架构和 API，我们可以从我们的程序中调用 TVP 存储过程，如下所示：
// Curry the TVP data
DataTable t1Ids = new DataTable( );
t1Ids.Columns.Add( "t1Id",
typeof( int ) );
int[] listOfIdsToFind = new[] {1, 5, 9};
foreach ( int id in listOfIdsToFind )
{
t1Ids.Rows.Add( id );
}
// Prepare the connection details
SqlConnection testbedConnection =
new SqlConnection(
@"Data Source=.\SQLExpress;Initial Catalog=TestbedDb;Persist Security Info=True;User ID=testbedUser;Password=letmein12;Connect Timeout=5" );
try
{
testbedConnection.Open( );
// Prepare a call to the stored procedure
SqlCommand findMatchingRowsInTable1 = new SqlCommand( "dbo.FindMatchingRowsInTable1",
testbedConnection );
findMatchingRowsInTable1.CommandType = CommandType.StoredProcedure;
// Curry up the TVP parameter
SqlParameter sqlParameter = new SqlParameter( "Table1Ids",
t1Ids );
findMatchingRowsInTable1.Parameters.Add( sqlParameter );
// Execute the stored procedure
SqlDataReader sqlDataReader = findMatchingRowsInTable1.ExecuteReader( );
while ( sqlDataReader.Read( ) )
{
Console.WriteLine( "Matching t1ID: {0}",
sqlDataReader[ "t1Id" ] );
}
}
catch ( Exception e )
{
Console.WriteLine( e.ToString( ) );
}
/* Output:
* Matching t1ID: 1
* Matching t1ID: 5
* Matching t1ID: 9
*/
There is probably a less painful way to do this using a more abstract API, such as Entity Framework. However, I do not have the time to see for myself at this time.
使用更抽象的 API（例如 Entity Framework）可能有一种不那么痛苦的方法来做到这一点。不过，我现在没有时间亲眼看看。
回答by KM.
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 using the link, and use it like:
使用链接创建一个拆分函数，并像这样使用它：
DELETE YourTable
FROM YourTable d
LEFT OUTER JOIN dbo.splitFunction(@Parameter) s ON d.ID=s.Value
WHERE s.Value IS NULL
I prefer the number table approach
This is code based on the above link that should do it for you...
这是基于上述链接的代码，应该为您完成...
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 dbo.FN_ListToTable(',',@YourString) s ON YourTable.ID = s.ListValue
here is your delete:
这是您的删除：
DELETE YourTable
FROM YourTable d
LEFT OUTER JOIN dbo.FN_ListToTable(',',@Parameter) s ON d.ID=s.ListValue
WHERE s.ListValue IS NULL
回答by Adriaan Stander
You could try this:
你可以试试这个：
DECLARE @List VARCHAR(MAX)
SELECT @List = '1,2,3,4,5,6,7,8'
EXEC(
'DELETE
FROM TABLE
WHERE ID NOT IN (' + @List + ')'
)
回答by Vitalivs
declare @ids nvarchar(1000)
set @ids = '100,2,3,4,5' --Parameter passed
set @ids = ',' + @ids + ','
select *
from TableName
where charindex(',' + CAST(Id as nvarchar(50)) + ',', @ids) > 0
回答by Simon
You could use a temp table which the stored procedure expects to exist. This will work on older versions of SQL Server, which do not support XML etc.
您可以使用存储过程期望存在的临时表。这将适用于不支持 XML 等的较旧版本的 SQL Server。
CREATE TABLE #temp
(INT myid)
GO
CREATE PROC myproc
AS
BEGIN
DELETE YourTable
FROM YourTable
LEFT OUTER JOIN #temp T ON T.myid=s.id
WHERE s.id IS NULL
END
回答by Scott Ivey
I'd consider passing your IDs as an XML string, and then you could shred the XML into a temp table to join against, or you could also query against the XML directly using SP_XML_PREPAREDOCUMENTand OPENXML.
我会考虑将您的 ID 作为 XML 字符串传递，然后您可以将 XML 切碎到临时表中以进行连接，或者您也可以直接使用SP_XML_PREPAREDOCUMENT和OPENXML查询 XML 。
回答by Cody C
What about using the XML data type instead of passing an array. I find that a better solution and works well in SQL 2005
如何使用 XML 数据类型而不是传递数组。我发现一个更好的解决方案并且在 SQL 2005 中运行良好
回答by Egbert Nierop
I like this one, because it is suited to be passed as an XElement, which is suitable for SqlCommand
我喜欢这个，因为它适合作为XElement传递，适合SqlCommand
(Sorry it is VB.NET but you get the idea)
（对不起，它是 VB.NET，但你明白了）
<Extension()>
Public Function ToXml(Of T)(array As IEnumerable(Of T)) As XElement
Return XElement.Parse(
String.Format("<doc>{0}</doc>", String.Join("", array.Select(Function(s) String.Concat("<d>", s.ToString(), "</d>")))), LoadOptions.None)
End Function
This is the sql Stored proc, shortened, not complete!
这是sql Stored proc，缩短了，不完整！
CREATE PROCEDURE [dbo].[myproc]
(@blah xml)
AS
... WHERE SomeID IN (SELECT doc.t.value('.','int') from @netwerkids.nodes(N'/doc/d') as doc(t))
CREATE PROCEDURE [dbo].[myproc] (@blah xml)
AS ... WHERE SomeID IN (SELECT doc.t.value('.','int') from @netwerkids.nodes(N'/doc/d') ) 作为文档(t))
回答by Jovan MSFT
In SQL Server 2016 you can wrap array with [ ] and pass it as JSON see http://blogs.msdn.com/b/sqlserverstorageengine/archive/2015/09/08/passing-arrays-to-t-sql-procedures-as-json.aspx
在 SQL Server 2016 中，您可以使用 [] 包装数组并将其作为 JSON 传递，请参阅http://blogs.msdn.com/b/sqlserverstorageengine/archive/2015/09/08/passing-arrays-to-t-sql-procedures -as-json.aspx
