Gültigkeit temporärer Tabellen bei dynamischem SQL

Eine Prozedur erstellt eine temporäre Tabelle mit dynamischem SQL und liest sie anschließend außerhalb dieses Batches. Das Einfügen funktioniert, doch die nächste Abfrage meldet einen ungültigen Objektnamen. Häufig liegt es am Gültigkeitsbereich: Ein äußeres temporäres Objekt ist innen sichtbar, ein innen erzeugtes Objekt überlebt diesen Bereich jedoch nicht.

Den Besitzer im äußeren Bereich festlegen

Das Beispiel erzeugt #OuterWork im aufrufenden Batch. Dynamisches SQL fügt dort eine Zeile ein und erstellt zusätzlich #InnerWork. Nach dessen Ende bleibt #OuterWork verfügbar. Eine weitere dynamische Abfrage auf #InnerWork erzeugt Fehler 208, den TRY/CATCH auffängt.

CREATE TABLE #OuterWork (ItemId int NOT NULL PRIMARY KEY);

EXEC sys.sp_executesql N'
    INSERT #OuterWork (ItemId) VALUES (@Id);
    CREATE TABLE #InnerWork (ItemId int NOT NULL);
    INSERT #InnerWork VALUES (99);
    SELECT ItemId AS VisibleInside FROM #InnerWork;',
    N'@Id int', @Id = 7;

SELECT ItemId AS VisibleOutside FROM #OuterWork;

BEGIN TRY
    EXEC sys.sp_executesql N'SELECT ItemId FROM #InnerWork;';
END TRY
BEGIN CATCH
    SELECT ERROR_NUMBER() AS ErrorNumber,
           ERROR_MESSAGE() AS ErrorMessage;
END CATCH;

DROP TABLE #OuterWork;

Das erste Ergebnis enthält 99, das zweite 7, und das letzte beschreibt das fehlende Objekt. Es wird keine zweite Verbindung verwendet. Die Grenze liegt innerhalb derselben Sitzung; die Verbindung offen zu halten bewahrt die innere Tabelle daher nicht.

Wenn nachfolgende Anweisungen die Daten benötigen, erstellen Sie die Tabelle mit ausdrücklichem Schema außen und lassen Sie den dynamischen Batch sie befüllen. Der Vertrag ist dann sichtbar: Der Aufrufer bestimmt die Struktur, die innere Verarbeitung liefert Daten, und der Aufrufer liest und entfernt sie.

Ähnliches gilt für gespeicherte Prozeduren. Eine dort erstellte lokale temporäre Tabelle verschwindet beim Ende der Prozedur, kann aber währenddessen von verschachtelten Prozeduren verwendet werden. Eine Prozedur kann auch eine Tabelle ihres Aufrufers nutzen. Diese implizite Abhängigkeit sollte jedoch mit Spalten und Constraints dokumentiert werden.

Variablen und Verbindungen unterscheiden

sp_executesql führt einen eigenen Batch aus. Normale skalare Variablen des Aufrufers sind dort nicht automatisch verfügbar. Übergeben Sie Werte als typisierte Parameter, wie @Id im Beispiel. Die Sichtbarkeit einer bereits existierenden temporären Tabelle folgt einer anderen Regel. Diese Unterscheidung vermeidet unnötige Zeichenkettenverkettung.

Auch eine Tabellenvariable ist im dynamischen Batch nicht allein deshalb sichtbar, weil ihre Deklaration direkt davor steht. Für eine mengenförmige Eingabe kann ein Table-Valued Parameter einen ausdrücklichen Vertrag liefern. Für veränderliche Zwischenergebnisse, die verschachtelte dynamische Verarbeitung teilt, ist die äußere temporäre Tabelle oft einfacher.

Dynamische Spaltennamen sind wiederum etwas anderes als variable Werte. Ein Ausgabeschema, das bei jeder Anfrage wechselt, erschwert nachfolgende statische SQL-Anweisungen und Clientcode. Prüfen Sie, ob das dynamische Ergebnis direkt zurückgegeben werden kann oder wechselnde Eigenschaften besser als Zeilen mit stabilen Spalten erscheinen.

Lokale temporäre Tabellen gehören zu einer physischen SQL-Sitzung. Zwei Anwendungsaufrufe erhalten nicht garantiert dieselbe Verbindung aus dem Pool. Eine Tabelle für den nächsten HTTP-Aufruf aufzubewahren ist deshalb keine zuverlässige Zustandsverwaltung, selbst wenn ein einfacher Test zufällig funktioniert.

Verdeckte gemeinsame Nutzung vermeiden

Die Umbenennung von #Work in ##Work erzeugt eine globale temporäre Tabelle und ändert Sichtbarkeit sowie Lebensdauer. Das verlängert nicht bloß eine lokale Variable. Gleichzeitige Anfragen können über Namen kollidieren oder fremde Daten sehen. Für aufrufübergreifende Arbeit ist eine dauerhafte Staging-Tabelle mit eindeutiger Job-ID häufig klarer.

Vermeiden Sie gleiche temporäre Tabellennamen in verschachtelten Bereichen. SQL Server kann solche Objekte gleichzeitig besitzen, was Namensauflösung und Wartung überraschend macht. Geben Sie verschiedenen Aufgaben verschiedene Namen, statt sich auf eine zufällig passend aufgelöste Referenz zu verlassen.

Temporärer Speicher ist außerdem begrenzt. Breite Zeilen, Indizes und lange Sitzungen können tempdb dauerhaft belasten. Entfernen Sie große Zwischenobjekte nach ihrer letzten Verwendung, besonders wenn eine lange Prozedur danach andere Aufgaben ausführt. Berücksichtigen Sie auch, wie ein Transaktionsrollback die eingefügten Daten beeinflusst.

Testen Sie verschachtelte Prozeduren, dynamische Batches, Fehlerpfade und getrennte Poolverbindungen über den tatsächlichen Anwendungspfad. Ein klarer Besitzer und eine bekannte Lebensdauer machen das Verhalten nachvollziehbar. Damit wird aus einem scheinbar sporadischen Objektfehler eine überprüfbare Schnittstellenregel.

Technische Referenzen: Microsoft Learn: CREATE TABLE and temporary scope · Microsoft Learn: sp_executesql.