将参数数组传递到存储过程。

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?

我怎么能用最简单的方法做这件事呢?

10 个解决方案

#1

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

---

#2

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?_Ĭ¼Ð)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(如实体框架)来实现这一点可能没有那么痛苦。但是，我现在没有时间亲自去看。

#3

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:

使用链接创建一个split函数，并使用它:

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:

在使用我的函数之前，需要设置一个“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

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

#4

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 + ')'
)

#5

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.

您可以使用存储过程预期存在的临时表。这将适用于较旧版本的SQL Server，它不支持XML等。

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

#6

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

#7

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_PREPAREDOCUMENT and OPENXML.

我将考虑将您的id作为XML字符串传递，然后您可以将XML分解成一个临时表来连接，或者您也可以使用SP_XML_PREPAREDOCUMENT和OPENXML直接查询XML。

#8

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中运行良好。

#9

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。但你懂的)

<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存储的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))

创建过程[dbo]。[myproc] (@blah xml)其中@netwerkids.nodes(N'/doc/d')中的SomeID(选择doc.t.value('.'，'int')作为doc(t))

#10

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。