将参数数组传递给存储过程 [英] Passing an array of parameters to a stored procedure

问题描述

我需要将id"数组传递给存储过程，以从表中删除所有行，除了与数组中的 id 匹配的行.

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.

我怎样才能以最简单的方式做到这一点?

How can I do it in a most simple way?

推荐答案

使用存储过程:

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

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

结果(可与 XML 参数一起使用):

The result (ready to use with XML parameter):

<?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 作为参数传递:

Passing XML as parameter:

<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

这篇关于将参数数组传递给存储过程的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！