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

Wie bereits von anderen erwähnt, besteht eine Möglichkeit darin, das Array in eine Zeichenkette zu konvertieren und die Zeichenkette dann in SQL Server aufzuteilen.

Seit SQL Server 2016 gibt es eine eingebaute Methode zum Aufteilen von Strings, die

STRING_SPLIT()

Sie gibt eine Reihe von Zeilen zurück, die Sie in Ihre temporäre Tabelle (oder echte Tabelle) einfügen können.

DECLARE @str varchar(200)
SET @str = "123;456;789;246;22;33;44;55;66"
SELECT value FROM STRING_SPLIT(@str, ';')

ergeben würde:

value
-----
  123
  456
  789
  246
   22
   33
   44
   55
   66

Wenn Sie etwas ausgefallener werden wollen:

DECLARE @tt TABLE (
    thenumber int
)
DECLARE @str varchar(200)
SET @str = "123;456;789;246;22;33;44;55;66"

INSERT INTO @tt
SELECT value FROM STRING_SPLIT(@str, ';')

SELECT * FROM @tt
ORDER BY thenumber

würden Sie die gleichen Ergebnisse wie oben erhalten (außer dass der Spaltenname "thenumber" lautet), aber sortiert. Sie können die Tabellenvariable wie jede andere Tabelle verwenden, so dass Sie sie leicht mit anderen Tabellen in der DB verknüpfen können, wenn Sie wollen.

Beachten Sie, dass Ihre SQL Server-Installation auf Kompatibilitätsebene 130 oder höher sein muss, damit die STRING_SPLIT() Funktion erkannt werden. Sie können Ihre Kompatibilitätsstufe mit der folgenden Abfrage überprüfen:

SELECT compatibility_level
FROM sys.databases WHERE name = 'yourdatabasename';

Die meisten Sprachen (einschließlich C#) haben eine "join"-Funktion, mit der Sie eine Zeichenkette aus einem Array erstellen können.

int[] myarray = {22, 33, 44};
string sqlparam = string.Join(";", myarray);

Dann passieren Sie sqlparam als Parameter für die obige gespeicherte Prozedur.

Answer 2

Das wird Ihnen helfen :) Folgen Sie den nächsten Schritten,

1. Öffnen Sie den Abfrage-Editor

2. Kopieren Sie den folgenden Code und fügen Sie ihn so ein, dass die Funktion erstellt wird, die den String in Int umwandelt

   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

3. Erstellen Sie die folgende gespeicherte Prozedur

    CREATE PROCEDURE dbo.sp_DeleteMultipleId
    @List VARCHAR(MAX)
    AS
    BEGIN
         SET NOCOUNT ON;
         DELETE FROM TableName WHERE Id IN( SELECT Id = Item FROM dbo.SplitInts(@List, ',')); 
    END
    GO

4. Führen Sie diesen SP aus mit exec sp_DeleteId '1,2,3,12' dies ist eine Kette von Id's, die Sie löschen möchten,

5. Sie können Ihr Array in C# in eine Zeichenkette umwandeln und diese als Parameter einer Stored Procedure wie folgt übergeben,

   int[] intarray = { 1, 2, 3, 4, 5 };  
   string[] result = intarray.Select(x=>x.ToString()).ToArray();
   
   SqlCommand command = new SqlCommand();
   command.Connection = connection;
   command.CommandText = "sp_DeleteMultipleId";
   command.CommandType = CommandType.StoredProcedure;
   command.Parameters.Add("@Id",SqlDbType.VARCHAR).Value=result ;

Damit werden mehrere Zeilen in einem einzigen Stored-Proc-Aufruf gelöscht. Ich wünsche Ihnen alles Gute.

Answer 3

Es gibt keine Unterstützung für Array in Sql Server, aber es gibt mehrere Möglichkeiten, mit denen Sie Sammlung zu einem gespeicherten proc übergeben können.

1. Durch die Verwendung von datatable
2. Versuchen Sie, Ihre Sammlung in ein XML-Format zu konvertieren und sie dann als Eingabe für eine gespeicherte Prozedur zu verwenden.

Der folgende Link könnte Ihnen helfen

Übergabe der Sammlung an eine gespeicherte Prozedur

Answer 4

Ab SQL Server 2016 können Sie die Liste als NVARCHAR() einbringen und OPENJSON verwenden

DECLARE @EmployeeList nvarchar(500) = '[1,2,15]'

SELECT * 
FROM Employees
WHERE ID IN (SELECT VALUE FROM OPENJSON(@EmployeeList ))

Answer 5

Ich habe durch alle Beispiele und Antworten, wie man ein beliebiges Array an Sql-Server ohne die Mühe der Erstellung neuer Tabelle Typ übergeben, bis ich diese gefunden linK Im Folgenden wird beschrieben, wie ich es auf mein Projekt angewendet habe:

--Der folgende Code erhält ein Array als Parameter und fügt dessen Werte ein --Array in eine andere Tabelle einfügen

Create Procedure Proc1 

@UserId int, //just an Id param
@s nvarchar(max)  //this is the array your going to pass from C# code to your Sproc

AS

    declare @xml xml

    set @xml = N'<root><r>' + replace(@s,',','</r><r>') + '</r></root>'

    Insert into UserRole (UserID,RoleID)
    select 
       @UserId [UserId], t.value('.','varchar(max)') as [RoleId]

    from @xml.nodes('//root/r') as a(t)
END 

Viel Spaß damit