Wie kann man ein Array an eine Gespeicherte Prozedur übergeben? In SQL gibt es keinen Datentyp 'Array'. Ein Möglichkeit bietet die Funktion 'OpenXML'. Wir übergeben der Prozedur einfach einen Text in XML-Form und die Prozedur Splittet diesen XML-Stream wieder auf.
Was Benötigen wir für dieses Vorhaben?
In der Gespeicherten Prozedur:
- einen Parameter @Array vom Typ 'varchar' als Input für unseren XML-Stream
- eine lokale Variable @iArray als Handle für das XML-Dokument
- die Procedur 'sp_xml_preparedocument' um das Handle zu erstellen
- die Procedur 'sp_xml_removedocument' um das Handle wieder zu löschen
Dann könnte unsere Prozedur wie Folgt aussehen:
|
CREATE PROCEDURE dbo.Array_An_SP
(
@Array varchar(4096)
)
AS
DECLARE @i int
DECLARE @iArray int
SET @i = 0
EXEC sp_xml_preparedocument @iArray OUTPUT, @Array
while (@i < 3)
BEGIN
-- Eine Variable für den Wert deklarieren
DECLARE @Value Char(10)
-- Den aktuellen Wert aus dem XML-File holen
SELECT @Value = value
FROM OPENXML (@iArray , '/root/array', 1 )
WITH (id INT, value char(10))
WHERE id = @i
--HIER STEHT DER CODE, z.B. ein INSERT-Statement für jeden Value im Array
-- Laufvariable um eins erhöhen
SET @i = @i +1
END
EXEC sp_xml_removedocument @iArray
RETURN
GO
|
Dann brauchen wir nur noch ein passenden 'String', den wir der Gespeicherten Prozedur als Parameter übergeben. Dieser muss folgende Form haben, um von der Prozedur verarbeitet werden zu können:
<root><array id="[Ihre ID1]" value="[Ihr Wert1]"></array><array id="[Ihre ID2]" value="[Ihr Wert2]"></array></root> |
Die Funktion OpenXML arbeitet bei relativ kleinen Datenmengen sehr performant. Bei sehr größeren Datenmengen ist diese Art der übergabe nicht mehr zu empfehlen.
ACHTUNG: OpenXML ist Key-Sensitive! Also immer auf GROß/klein-Schreibung achten.
Der Code für den Aufruf der Gespeicherten Prozedur und der Aufbau der XML-Textes:
Dim sb As New System.Text.StringBuilder
Dim i As Int32 = 0
sb.Append("<root>")
'Hier muss Ihr Code stehen
For Each dgItem In DG_ALLE.Items
If CType(dgItem.FindControl("gewaehlt"), CheckBox).Checked Then
'Das Anfügen an den String
sb.Append("<array id=""" & i.ToString & """ value=""" & DG_ALLE.DataKeys.Item(dgItem.ItemIndex).ToString & """></array>")
i += 1
End If
Next
sb.Append("</root>")
Dim updCom As New SqlCommand("Array_An_SP", mycon)
updCom.CommandType = CommandType.StoredProcedure
updCom.Parameters.Add("@Array", SqlDbType.VarChar, 4096).Value = sb.ToString
mycon.Open()
updCom.ExecuteNonQuery()
mycon.Close() |
Sinnvoll kann diese Vorgehensweise sein, wenn Sie z.B. vor einem 'INSERT' erst alle Datensätze löschen wollen. So könne Sie in einer Gespeicherten Prozedur die Datensätze löschen und dann wieder einfügen. Das ist sehr viel sicherer als mit zwei verschiedenen SQL Kommandos zu arbeiten, denn es besteht die Gefahr, das Ihr Programm zwischen dem ersten und zweiten Kommando abstürzt und folglich alle Datensätze gelöscht sind, aber keine Neuen eingefügt worden sind.
