Arbeiten mit Tabellenvariablen in MSSQL

Marco Betschart, Webapplikations-Programmierer, bis 2010

Beim Erstellen einer komplexen Abfrage für ein Newslettersystem bin ich über ein nettes Feature von MSSQL gestolpert. Es gibt nämlich die Möglichkeit, mit Tabellenvariablen zu Arbeiten. Aber zuerst zur Aufgabenstellung…

Als Ausgangslage für das Newslettersystem dient eine Mitgliederdatenbank, in der die verschiedenen Mitglieder und Mitgliedertype hinterlegt sind. Nun kann es vorkommen, dass zum Beispiel zwei Personen pro Firma angeschrieben werden sollen. Pro Firma kann allerdings nur eine Kontaktadresse hinterlegt werden – der Wunsch nach einer Komma separierten Adressliste kam auf. Da die Newsletter mit dem cms3 Newslettermodul versendet werden, waren bereits einige Kundenspezifische Abfragen eingerichtet, die nun so erweitert werden mussten, dass sie mit Listen zurecht kamen. Das bisherige Query sah also zum Beispiel folgendermassen aus:

1SELECT Email AS strEmail FROM tblCompany
2 WHERE Email != '' AND intSektion = 18
3ORDER BY Email

Da ich für alle Einträge des bisherigen SELECT-Statements Stringoperationen ausführen musste, musste ein Cursor her:

1DECLARE curEmail CURSOR GLOBAL READ_ONLY FAST_FORWARD FOR
2 SELECT Email AS strEmail FROM tblCompany
3 WHERE Email != '' AND intSektion = 18
4 ORDER BY Email
5
6OPEN curEmail
7WHILE 1 = 1
8BEGIN
9 IF @@FETCH_STATUS < 0 BREAK
10END
11CLOSE curEmail
12DEALLOCATE curEmail

Soweit so gut. Jetzt fehlt noch die Listenbearbeitung. Jeder Eintrag kann eine Liste mit mehreren E-Mail-Adressen sein. Diese gilt es nun zu extrahieren und zwar mit folgendem Codeschnipsel:

1FETCH NEXT FROM curEmail INTO
2 @strEmail
3IF @@FETCH_STATUS < 0 BREAK
4 SET @strEmail = REPLACE(@strEmail,';',',')
5 SET @strEmail = REPLACE(@strEmail,' ' ,',')
6 SET @intNextDelim = CHARINDEX(',',@strEmail)
7WHILE @intNextDelim >
0
8BEGIN
9 SET @strTemp = LEFT(@strEmail,@intNextDelim)
10 SET @strEmail = REPLACE(@strEmail,@strTemp,'')
11 SET @strTemp = REPLACE(@strTemp,',','')
12 SET @intNextDelim = CHARINDEX(',',@strEmail)
13END

Jetzt befindet sich die jeweils aktuellste E-Mail-Adresse in der Variable @strTemp – aber wohin damit? Eine Möglichkeit wäre eine temporäre Tabelle, die E-Mail-Adressen einfügen und danach wieder auslesen… Ich dachte mir, dass das ein wenig aufwendig für diese Art von Aufgabe ist. Das muss doch irgendwie einfacher gehen? Und es geht auch einfacher! MSSQL bietet da eine einfache Lösung, mit einer "temporären" Tabelle zu Arbeiten: Man deklariert die Tabelle einfach als Variable. Wie das geht? Ganz einfach:

1DECLARE @Newsletter_Mail TABLE (
2    strEmail VARCHAR(255)
3)

Mit dieser Tabelle kann wie mit einer normalen Tabelle gearbeitet werden. Das heisst im Klartext, es sind Insert-, Update- und Delete-Aktionen möglich! Das komplette Beispiel sieht dann folgendermassen aus:

1DECLARE @Newsletter_Mail TABLE (
2    strEmail VARCHAR(255)
3)
4
5DECLARE curEmail CURSOR GLOBAL READ_ONLY FAST_FORWARD FOR
6 SELECT Email AS strEmail FROM tblCompany
7 WHERE Email != '' AND intSektion = 18
8 ORDER BY Email
9
10OPEN curEmail
11WHILE 1 = 1
12BEGIN
13 FETCH NEXT FROM curEmail INTO
14 @strEmail
15 IF @@FETCH_STATUS < 0 BREAK
16 SET @strEmail = REPLACE(@strEmail,';',',')
17 SET @strEmail = REPLACE(@strEmail,' ' ,',')
18 SET @intNextDelim = CHARINDEX(',',@strEmail)
19
20 WHILE @intNextDelim > 0
21 BEGIN
22 SET @strTemp = LEFT(@strEmail,@intNextDelim)
23 SET @strEmail = REPLACE(@strEmail,@strTemp,'')
24 SET @strTemp = REPLACE(@strTemp,',','')
25 SET @intNextDelim = CHARINDEX(',',@strEmail)
26
27 INSERT INTO @Newsletter_Mail (
28 strEmail
29 ) VALUES (
30 @strTemp
31 )
32 END
33 INSERT INTO @Newsletter_Mail (
34 strEmail
35 ) VALUES (
36 @strEmail
37 )
38 END
39END
40CLOSE curEmail
41DEALLOCATE curEmail
42
43SELECT strEmail FROM @Newsletter_Mail