Communardo Software GmbH, Kleiststraße 10 a, D-01129 Dresden

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:
    blank
  2. eine SQL Server Prozedur oder Funktion erstel­len, die den neuen Typ als Input-Parameter verwendet:
    blank
  3. eine Variable von die­sem Typ erzeu­gen und mit Daten befüllen:
    blank
  4. die Variable an die Prozedur oder Funktion übergeben:
    blank

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();

21. November 2008
||

Related Posts

Hallo Dorrit,

..CREATE TYPE wird je Datenbank defi­niert. Wenn man Prozedur aus einer ande­ren DB auf­ruft, muss dann der TVP in bei­den DB's defi­niert sein? bestimmt…

blank Dorrit Riemenschneider

Hallo - abso­lut kor­rekt, benut­zer­de­fi­nierte Datentypen sind lokal für eine Datenbank. Wenn ich einen TVP als Parameter an eine Prozedur aus einer ande­ren Datenbank über­ge­ben will, muss er in der auf­ru­fen­den DB eben­falls regis­triert sein. Details s. Datenbankübergreifendes Verwenden benut­zer­de­fi­nier­ter Datentypen

Viele Grüße, Dorrit

Comments are closed.

Pin It on Pinterest