temporäre Tabellen in Stored Procedure

[goof]

Hallo,

bin hier gerade fast am verzweifeln. Ich schreibe eine Stored Procedure, in der ich zu nächst Daten aus einer Excel-Tabelle einlese. Um mit einer variablen Anzahl an Spalten umgehen zu können lege ich zunächst eine temporäre Tabelle mit der gleichen Zahl an Spalten an. In dieser will ich dann die Daten mittels Bulk Insert einlesen. Allerdings bekomme ich dann die Fehlermeldung, dass der Tabellenname ungültig ist. Das Grundproblem sieht somit folgerndermaßen aus:

CREATE PROC test
AS
DECLARE @Query VARCHAR(MAX);
SET @Query = 'CREATE TABLE #tmp (S1 INT, S2 VARCHAR(MAX))';
EXEC(@Query);

SET @Query='INSERT INTO #tmp VALUES (0, 'x')';
EXEC(@Query);

Beim letzten Statement könnte man auch ebenso SELECT * FROM #tmp schreiben, in jedem Fall kommt die Fehlermeldung: "Ungültiger Objektname '#tmp'.".

[hwoess]

Eine temporäre Tabelle ist nur ab dem create in der "create"-Prozedur und in von dieser aufgerufenen Unterprozeduren sichtbar. Da EXEC eine eigene Unterprozedur ist, ist die temporäre Tabelle in deiner Hauptprozedur "test" daher nicht sichtbar, weil diese ja "darüber" liegt. Und gleich nach Rückkehr aus diesem EXEC ist die temporäre Tabelle auch wieder weg, weil sie mit dem Prozess, der das create gemacht hat, mitstirbt.
Daher geht ein nachfolgendes "insert into #tmp ... " natürlich daneben.
Lösung: das CREATE table #tmp direkt und nicht über EXEC machen, dann klappt auch der Rest.

bye,
Helmut

[goof]

Hallo Helmut,

vielen Dank. Da ich leider gerade die Dynamik brauche habe ich auch den Rest mit in den Query-Ausdruck geschrieben, der mit einem Mal in Exec ausgeführt wird. Es ist zwar nicht schön, allerdings ist mir ersteinmal keine andere Lösung eingefallen.