… oder wie bei Microsoft das Basteln abgeschafft wurde
Wer schon einmal vor dem Problem stand, an eine SQL Server Prozedur oder Funktion eine Liste von Werten oder gar eine Tabelle zu übergeben, der wird für dieses Problem bestimmt eine Lösung gefunden haben: Ob man nun die Werte mit einem geeigneten Trennzeichen versehen (ich bevorzuge Pipes 
) alle in eine varchar(max)-Variable packt und mittels einer benutzerdefinierten Funktion wieder “auseinanderschnipselt” oder gleich den XML-Datentyp verwendet - es gibt die verschiedensten Bastellösungsansätze, dieser Problematik Herr zu werden.
SQL Server 2008 offeriert mit Table Valued Parameters diese Funktionalität nun gänzlich ohne Bastelei. Die Technik dafür ist denkbar simpel:
Es wird angenommen, dass eine Tabelle Employees mit folgender Struktur existiert:
Nun muss man
- einen benutzerdefinierter Datentyp vom Typ Table erzeugen:
- eine SQL Server Prozedur oder Funktion erstellen, die den neuen Typ als Input-Parameter verwendet:
- eine Variable von diesem Typ erzeugen und mit Daten befüllen:
- die Variable an die Prozedur oder Funktion übergeben:
Fertig
Für alle, die es gerne schnell mal ausprobieren möchten, hier nochmal zum Kopieren:
CREATE TABLE Employees(
EmployeeID int NOT NULL,
LastName nvarchar(50) NOT NULL,
FirstName nvarchar(50) NOT NULL)
go
CREATE TYPE T_Employees AS TABLE (EmployeeID int, LastName nvarchar(50), FirstName nvarchar(50))
go
CREATE PROCEDURE P_InsertEmployees
@employees T_Employees READONLY
AS
INSERT INTO Employees (EmployeeID, LastName, FirstName)
SELECT EmployeeID, LastName, FirstName
FROM @employees
go
DECLARE @emps T_Employees
INSERT INTO @emps (EmployeeID, LastName, FirstName) VALUES (1, ‘Davolio’, ‘Nancy’)
INSERT INTO @emps (EmployeeID, LastName, FirstName) VALUES (2, ‘Fuller’, ‘Andrew’)
EXEC P_InsertEmployees @emps
Hier noch ein paar Dinge, die zu beachten bzw. wissenswert sind:
- Der INPUT-Parameter für die Prozedur muss READONLY sein.
- TVP können nicht als Return-Variable für eine Funktion verwendet werden.
- Microsoft empfielt die Verwendung von TVP bis zur Anzahl von 1000 Datensätzen, für alles, was drüber ist, sollte BULK INSERT verwendet werden.
- TVP werden als temporäre Tabellen in der tempdb gespeichert.
Und zum Abschluss noch ein kleiner Leckerbissen für ADO.Net Programmierer: TVP werden auch hier vollständig unterstützt, ein Beispiel für den Aufruf der oben erstellte Prozedur in C# könnte so aussehen:
SqlConnection sqlConn…
DataTable dt = new DataTable();
dt.Columns.Add….
dt.Rows.Add…..
SqlCommand cmd = new SqlCommand(”P_InsertEmployees”, sqlConn);
cmd.Parameters.AddWithValue(”@employees”, dt);
cmd.ExecuteNonQuery();
. 
