Wie man ein Array an eine gespeicherte SQL Server-Prozedur übergibt

Question

Wie übergibt man ein Array an eine SQL Server Stored Procedure?

Ich habe zum Beispiel eine Liste von Mitarbeitern. Ich möchte diese Liste als Tabelle verwenden und sie mit einer anderen Tabelle verbinden. Aber die Liste der Mitarbeiter sollte als Parameter von C# übergeben werden.

Answer 1

SQL Server 2016 (oder neuer)

Sie können eine abgegrenzte Liste oder JSON und verwenden STRING_SPLIT() o OPENJSON() .

STRING_SPLIT() :

CREATE PROCEDURE dbo.DoSomethingWithEmployees
  @List varchar(max)
AS
BEGIN
  SET NOCOUNT ON;

  SELECT value FROM STRING_SPLIT(@List, ',');
END
GO
EXEC dbo.DoSomethingWithEmployees @List = '1,2,3';

OPENJSON() :

CREATE PROCEDURE dbo.DoSomethingWithEmployees
  @List varchar(max)
AS
BEGIN
  SET NOCOUNT ON;

  SELECT value FROM OPENJSON(CONCAT('["',
    REPLACE(STRING_ESCAPE(@List, 'JSON'), 
    ',', '","'), '"]')) AS j;
END
GO
EXEC dbo.DoSomethingWithEmployees @List = '1,2,3';

Mehr dazu habe ich hier geschrieben:

• Behandlung einer unbekannten Anzahl von Parametern in SQL Server
• Aufteilung von geordneten Zeichenfolgen in SQL Server mit OPENJSON

SQL Server 2008 (oder neuer)

Erstellen Sie zunächst in Ihrer Datenbank die folgenden beiden Objekte:

CREATE TYPE dbo.IDList
AS TABLE
(
  ID INT
);
GO

CREATE PROCEDURE dbo.DoSomethingWithEmployees
  @List AS dbo.IDList READONLY
AS
BEGIN
  SET NOCOUNT ON;

  SELECT ID FROM @List; 
END
GO

Jetzt in Ihrem C#-Code:

// Obtain your list of ids to send, this is just an example call to a helper utility function
int[] employeeIds = GetEmployeeIds();

DataTable tvp = new DataTable();
tvp.Columns.Add(new DataColumn("ID", typeof(int)));

// populate DataTable from your List here
foreach(var id in employeeIds)
    tvp.Rows.Add(id);

using (conn)
{
    SqlCommand cmd = new SqlCommand("dbo.DoSomethingWithEmployees", conn);
    cmd.CommandType = CommandType.StoredProcedure;
    SqlParameter tvparam = cmd.Parameters.AddWithValue("@List", tvp);
    // these next lines are important to map the C# DataTable object to the correct SQL User Defined Type
    tvparam.SqlDbType = SqlDbType.Structured;
    tvparam.TypeName = "dbo.IDList";
    // execute query, consume results, etc. here
}

SQL Server 2005

Wenn Sie SQL Server 2005 verwenden, würde ich trotzdem eine Split-Funktion gegenüber XML empfehlen. Erstellen Sie zunächst eine Funktion:

CREATE FUNCTION dbo.SplitInts
(
   @List      VARCHAR(MAX),
   @Delimiter VARCHAR(255)
)
RETURNS TABLE
AS
  RETURN ( SELECT Item = CONVERT(INT, Item) FROM
      ( SELECT Item = x.i.value('(./text())[1]', 'varchar(max)')
        FROM ( SELECT [XML] = CONVERT(XML, '<i>'
        + REPLACE(@List, @Delimiter, '</i><i>') + '</i>').query('.')
          ) AS a CROSS APPLY [XML].nodes('i') AS x(i) ) AS y
      WHERE Item IS NOT NULL
  );
GO

Jetzt kann Ihre gespeicherte Prozedur einfach sein:

CREATE PROCEDURE dbo.DoSomethingWithEmployees
  @List VARCHAR(MAX)
AS
BEGIN
  SET NOCOUNT ON;

  SELECT EmployeeID = Item FROM dbo.SplitInts(@List, ','); 
END
GO

Und in Ihrem C#-Code müssen Sie die Liste einfach als '1,2,3,12' ...

---

Ich finde, dass die Methode der Übergabe von tabellenwertigen Parametern die Wartbarkeit einer Lösung, die sie verwendet, vereinfacht und oft eine höhere Leistung im Vergleich zu anderen Implementierungen, einschließlich XML und String-Splitting, aufweist.

Die Eingaben sind klar definiert (niemand muss raten, ob das Trennzeichen ein Komma oder ein Semikolon ist), und wir haben keine Abhängigkeiten von anderen Verarbeitungsfunktionen, die nicht offensichtlich sind, ohne den Code der gespeicherten Prozedur zu inspizieren.

Im Vergleich zu Lösungen, die benutzerdefinierte XML-Schemata anstelle von UDTs verwenden, umfasst dies eine ähnliche Anzahl von Schritten, aber meiner Erfahrung nach ist der Code viel einfacher zu verwalten, zu pflegen und zu lesen.

In vielen Lösungen benötigen Sie vielleicht nur einen oder einige wenige dieser UDTs (User defined Types), die Sie für viele gespeicherte Prozeduren wiederverwenden. Wie in diesem Beispiel besteht die allgemeine Anforderung darin, eine Liste von ID-Zeigern durchzugeben, der Funktionsname beschreibt, welchen Kontext diese Ids darstellen sollen, der Typname sollte generisch sein.

Answer 2

Meiner Erfahrung nach gibt es für dieses Problem eine knifflige und schöne Lösung, indem man einen abgegrenzten Ausdruck aus den employeeIDs erstellt. Sie sollten nur einen String-Ausdruck erstellen wie ';123;434;365;' in dem 123 , 434 y 365 sind einige MitarbeiterIDs. Durch den Aufruf der unten stehenden Prozedur und die Übergabe dieses Ausdrucks können Sie die gewünschten Datensätze abrufen. Sie können die "andere Tabelle" ganz einfach in diese Abfrage einfügen. Diese Lösung ist für alle Versionen von SQL Server geeignet. Außerdem ist sie im Vergleich zur Verwendung von Tabellenvariablen oder temporären Tabellen eine sehr schnelle und optimierte Lösung.

CREATE PROCEDURE dbo.DoSomethingOnSomeEmployees  @List AS varchar(max)
AS
BEGIN
  SELECT EmployeeID 
  FROM EmployeesTable
  -- inner join AnotherTable on ...
  where @List like '%;'+cast(employeeID as varchar(20))+';%'
END
GO

Answer 3

Verwenden Sie einen tabellenwertigen Parameter für Ihre gespeicherte Prozedur.

Wenn Sie es von C# aus übergeben, fügen Sie den Parameter mit dem Datentyp SqlDb.Structured.

Siehe hier: http://msdn.microsoft.com/en-us/library/bb675163.aspx

Ejemplo:

// Assumes connection is an open SqlConnection object.
using (connection)
{
// Create a DataTable with the modified rows.
DataTable addedCategories =
  CategoriesDataTable.GetChanges(DataRowState.Added);

// Configure the SqlCommand and SqlParameter.
SqlCommand insertCommand = new SqlCommand(
    "usp_InsertCategories", connection);
insertCommand.CommandType = CommandType.StoredProcedure;
SqlParameter tvpParam = insertCommand.Parameters.AddWithValue(
    "@tvpNewCategories", addedCategories);
tvpParam.SqlDbType = SqlDbType.Structured;

// Execute the command.
insertCommand.ExecuteNonQuery();
}

Answer 4

Sie müssen ihn als XML-Parameter übergeben.

Edita: kurzer Code aus meinem Projekt, um Ihnen eine Vorstellung zu geben:

CREATE PROCEDURE [dbo].[GetArrivalsReport]
    @DateTimeFrom AS DATETIME,
    @DateTimeTo AS DATETIME,
    @HostIds AS XML(xsdArrayOfULong)
AS
BEGIN
    DECLARE @hosts TABLE (HostId BIGINT)

    INSERT INTO @hosts
        SELECT arrayOfUlong.HostId.value('.','bigint') data
        FROM @HostIds.nodes('/arrayOfUlong/u') as arrayOfUlong(HostId)

Dann können Sie die temporäre Tabelle zur Verknüpfung mit Ihren Tabellen verwenden. Wir haben arrayOfUlong als eingebautes XML-Schema definiert, um die Datenintegrität zu wahren, aber Sie müssen das nicht tun. Ich würde empfehlen, es zu verwenden, also hier ist ein schneller Code, um sicherzustellen, dass Sie immer ein XML mit Longs erhalten.

IF NOT EXISTS (SELECT * FROM sys.xml_schema_collections WHERE name = 'xsdArrayOfULong')
BEGIN
    CREATE XML SCHEMA COLLECTION [dbo].[xsdArrayOfULong]
    AS N'<xs:schema xmlns:xs="http://www.w3.org/2001/XMLSchema">
    <xs:element name="arrayOfUlong">
        <xs:complexType>
            <xs:sequence>
                <xs:element maxOccurs="unbounded"
                            name="u"
                            type="xs:unsignedLong" />
            </xs:sequence>
        </xs:complexType>
    </xs:element>
</xs:schema>';
END
GO

Answer 5

Der Kontext ist immer wichtig, wie zum Beispiel die Größe y Komplexität des Arrays. Für kleine bis mittelgroße Listen sind mehrere der hier gegebenen Antworten gut geeignet, obwohl einige Klarstellungen vorgenommen werden sollten:

• Zum Aufteilen einer begrenzten Liste ist ein SQLCLR-basierter Splitter am schnellsten. Es gibt zahlreiche Beispiele, wenn Sie Ihre eigenen schreiben wollen, oder Sie können einfach das kostenlose SQL# Bibliothek von CLR-Funktionen (die ich geschrieben habe, aber die String_Split-Funktion und viele andere sind völlig frei).
• Aufteilung XML-basierter Arrays peut schnell sein, aber Sie müssen attributbasiertes XML verwenden, nicht elementbasiertes XML (das ist der einzige Typ, der in den Antworten hier gezeigt wird, obwohl das XML-Beispiel von @AaronBertrand das beste ist, da sein Code die text() XML-Funktion. Weitere Informationen (z. B. eine Leistungsanalyse) über die Verwendung von XML zum Aufteilen von Listen finden Sie unter "Verwendung von XML zur Übergabe von Listen als Parameter in SQL Server". von Phil Factor.
• Die Verwendung von TVPs ist großartig (vorausgesetzt, Sie verwenden mindestens SQL Server 2008 oder neuer), da die Daten in den Proc gestreamt werden und vorparsed und stark typisiert als Tabellenvariable angezeigt werden. JEDOCH ist es in den meisten Fällen sinnvoll, alle Daten in DataTable bedeutet, dass die Daten im Speicher dupliziert werden, während sie aus der ursprünglichen Sammlung kopiert werden. Daher ist die Verwendung der DataTable Methode der Übergabe von TVPs funktioniert nicht gut für größere Datensätze (d.h. sie ist nicht gut skalierbar).
• XML kann im Gegensatz zu einfachen abgegrenzten Listen von Ints oder Strings mehr als eindimensionale Arrays verarbeiten, genau wie TVPs. Aber auch genau wie die DataTable TVP-Methode ist XML nicht gut skalierbar, da sich die Datenmenge im Speicher mehr als verdoppelt, da zusätzlich der Overhead des XML-Dokuments berücksichtigt werden muss.

Wenn die Daten, die Sie verwenden, groß sind oder noch nicht sehr groß sind, aber stetig wachsen, dann ist die IEnumerable TVP-Methode ist die beste Wahl, da sie die Daten an SQL Server streamt (wie die DataTable Methode), ABER erfordert keine Duplizierung der Sammlung im Speicher (im Gegensatz zu allen anderen Methoden). Ich habe in dieser Antwort ein Beispiel für den SQL- und C#-Code angegeben:

Wörterbuch an Stored Procedure T-SQL übergeben