Übergeben eines Arrays von Parametern an eine gespeicherte Prozedur

Verwenden Sie eine gespeicherte Prozedur:

EDIT:
Eine Ergänzung für die Liste serialisieren (oder irgendetwas anderes):

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

Ergebnis (ready to use mit 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>

ORIGINAL POST:

Übergabe von XML als 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

InformationsquelleAutor der Antwort Zanoni

dies ist die beste source:

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

erstellen Sie eine split-Funktion mit dem link, und verwenden Sie es wie:

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

Ich bevorzuge die Anzahl table-Ansatz

Dies ist der code, basierend auf den oben genannten link, dass sollte es für Sie tun...

Bevor Sie meine Funktion, die Sie benötigen, um eine "Hilfs" - Tabelle, müssen Sie nur tun dies einmal pro Datenbank:

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

verwenden Sie diese Funktion zum aufteilen der Zeichenfolge, die nicht loop und ist sehr schnell:

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

Sie können diese Funktion als eine Tabelle in einer join:

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

hier ist dein löschen:

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

InformationsquelleAutor der Antwort KM.

Könnte man eine temp-Tabelle, die die gespeicherte Prozedur erwartet, zu existieren. Dies funktioniert auch auf älteren Versionen von SQL Server, die nicht die Unterstützung von XML etc.

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

InformationsquelleAutor der Antwort Simon

Würde ich prüfen, die Weitergabe Ihrer IDs als XML-string, und dann könnte man aufteilen des XML in eine temporäre Tabelle zum join gegen, oder Sie können auch die Abfrage gegen das XML direkt mit SP_XML_PREPAREDOCUMENT und OPENXML.

InformationsquelleAutor der Antwort Scott Ivey

Ich mag dieses, weil es geeignet ist, die übergeben werden, als ein XElement, das geeignet ist, SqlCommand

(Sorry, es ist VB.NET aber Sie bekommen die Idee)

<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

Dies ist die sql-Gespeicherte Prozedur, gekürzt, nicht komplett!

CREATE PROCEDURE [dbo].[myproc]
(@bla, xml)

ALS
... WO SomeID IN (SELECT doc.t.Wert('.','int") von @netwerkids.Knoten(N'/doc/d') doc(t))

InformationsquelleAutor der Antwort Egbert Nierop