Neuerungen in SQL Server 2008: Table Valued Parameters (TVP)

… oder wie bei Microsoft das Basteln abge­schafft wurde
Wer schon ein­mal vor dem Problem stand, an eine SQL Server Prozedur oder Funktion eine Liste von Werten oder gar eine Tabelle zu über­ge­ben, der wird für die­ses Problem bestimmt eine Lösung gefun­den haben: Ob man nun die Werte mit einem geeig­ne­ten Trennzeichen ver­se­hen (ich bevor­zuge Pipes ) alle in eine varchar(max)-Variable packt und mit­tels einer benut­zer­de­fi­nier­ten Funktion wie­der "aus­ein­an­der­schnip­selt" oder gleich den XML-Datentyp ver­wen­det - es gibt die ver­schie­dens­ten Bastellösungsansätze, die­ser Problematik Herr zu werden. 

SQL Server 2008 offe­riert mit Table Valued Parameters diese Funktionalität nun gänz­lich ohne Bastelei. Die Technik dafür ist denk­bar simpel:

Es wird ange­nom­men, dass eine Tabelle Employees mit fol­gen­der Struktur existiert:

Nun muss man

1. einen benut­zer­de­fi­nier­ter Datentyp vom Typ Table erzeugen:
   
2. eine SQL Server Prozedur oder Funktion erstel­len, die den neuen Typ als Input-Parameter verwendet:
   
3. eine Variable von die­sem Typ erzeu­gen und mit Daten befüllen:
   
4. die Variable an die Prozedur oder Funktion übergeben:
   

Fertig

Für alle, die es gerne schnell mal aus­pro­bie­ren möch­ten, hier noch­mal 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 beach­ten bzw. wis­sens­wert sind:

• Der INPUT-Parameter für die Prozedur muss READONLY sein.
• TVP kön­nen nicht als Return-Variable für eine Funktion ver­wen­det werden.
• Microsoft emp­fielt die Verwendung von TVP bis zur Anzahl von 1000 Datensätzen, für alles, was drü­ber ist, sollte BULK INSERT ver­wen­det werden.
• TVP wer­den als tem­po­räre Tabellen in der tempdb gespeichert.

Und zum Abschluss noch ein klei­ner Leckerbissen für ADO.Net Programmierer: TVP wer­den auch hier voll­stän­dig unter­stü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();