将参数数组传递给存储过程

我需要将一个“ id”数组传递给一个存储过程，以便从表中删除所有行(与数组中的 id 匹配的行除外)。

我怎样才能用最简单的方法做到呢？

132968 次浏览
小开

这是最好的来源:

Http://www.sommarskog.se/arrays-in-sql.html

使用链接创建一个拆分函数，并像下面这样使用它:

DELETE YourTable
FROM YourTable                           d
LEFT OUTER JOIN dbo.splitFunction(@Parameter) s ON d.ID=s.Value
WHERE s.Value IS NULL

我更喜欢用数字表的方法

这是基于以上链接的代码，应该为您做到这一点..。

在使用我的函数之前，您需要设置一个“ helper”表，每个数据库只需要这样做一次:

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

使用这个函数来分割你的字符串，它不循环，而且非常快:

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

您可以将此函数用作联接中的表:

SELECT
Col1, COl2, Col3...
FROM  YourTable
INNER JOIN dbo.FN_ListToTable(',',@YourString) s ON  YourTable.ID = s.ListValue

这是你的删除:

DELETE YourTable
FROM YourTable                                d
LEFT OUTER JOIN dbo.FN_ListToTable(',',@Parameter) s ON d.ID=s.ListValue
WHERE s.ListValue IS NULL
小开

如何使用 XML 数据类型而不是传递数组。我发现这是一个更好的解决方案，并且在 SQL2005中运行良好

小开

我会考虑将 ID 作为 XML 字符串传递，然后您可以将 XML 分解为一个临时表以便进行连接，或者您也可以直接使用 SP _ XML _ PREPAREDOCUMENT和 OPENXML查询 XML。

小开
最佳答案

使用存储过程:

编辑: 序列化 List (或其他任何东西)的补充:

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());

结果(可与 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>

原文:

将 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
小开

您可以使用存储过程期望存在的临时表。这将适用于不支持 XML 等的 SQLServer 旧版本。

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
小开

你可以试试这个:




DECLARE @List VARCHAR(MAX)


SELECT @List = '1,2,3,4,5,6,7,8'


EXEC(
'DELETE
FROM TABLE
WHERE ID NOT IN (' + @List + ')'
)
小开

如果您使用的是 Sql Server 2008或更高版本，那么可以使用表值参数(Table-Value Parameter，TVP)来代替每次将列表数据传递给存储过程时对其进行序列化和反序列化。

让我们从创建一个简单的模式开始，作为我们的游乐场:

CREATE DATABASE [TestbedDb]
GO




USE [TestbedDb]
GO


/* First, setup the sample program's account & credentials*/
CREATE LOGIN [testbedUser] WITH PASSWORD=N'µ×?
?S[°¿Q­¥½q?_Ĭ¼Ð)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

有了模式和示例数据，现在就可以创建 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

有了模式和 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
*/

使用更抽象的 API (如实体框架)来实现这一点，可能有一种不那么痛苦的方法。但是，我现在没有时间去亲眼看看。

小开 
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
小开

我喜欢这个，因为它适合作为 XElement 传递，这适合于 SqlCommand

(对不起，它是 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

这是 sql 存储过程，缩短，不完整！

创建过程[ dbo ] (@Blahxml)
作为 从@neterkids. node (N’/doc/d’)中选择 doc.t.value (’.’，‘ int’)作为 doc (t)

小开

在 SQLServer2016中，可以用[]包装数组，并将其作为 JSON 传递，请参见 http://blogs.msdn.com/b/sqlserverstorageengine/archive/2015/09/08/passing-arrays-to-t-sql-procedures-as-json.aspx

小开

您可以在 SQLServer 中使用 STRING _ SPLIT 函数。 您可以检查文档 给你。

DECLARE @YourListOfIds VARCHAR(1000) -- Or VARCHAR(MAX) depending on what you need


SET @YourListOfIds = '1,2,3,4,5,6,7,8'


SELECT * FROM YourTable
WHERE Id IN(SELECT CAST(Value AS INT) FROM STRING_SPLIT(@YourListOfIds, ','))

提交答案