Dynamic SQL to build #tblTemp_Filter_Table 1

[TonyBoex]

I am trying to run a stored procedure that initially takes a dynamic SQL input string (@query), proccesses the dynamic string which is supposed to create a temp table which is referred to by later segments in the stored procedure. Each time I run the procedure I get an error that says 'Invalid Object Name '#tblTemp_Filter_Table'. Any help would be greatly appreciated.


Declare @query varchar(1000)

Set @query='SELECT MishapID INTO #tblTemp_Filter_Table FROM tblMishaps WHERE tblMishaps.Aircraft_FK IN (''A4'',''AV8'',''C12'',''C130'',''C2'',''C9'') AND tblMishaps.Type_FK IN (''FM'',''FRM'',''AGM'') AND tblMishaps.Class_FK IN (''A'',''B'',''C'') AND tblMishaps.LocationID_FK IN (''ASH'',''EMB'',''DET'',''UNK'') AND tblMishaps.OrgID_FK IN (''USN'',''USMC'',''UNK'') AND Year(DateAdd(month,3,tblMishaps.MishapDate)) IN (1990, 1991, 1992, 1993, 1994, 1995, 1996) AND NOT(tblMishaps.DatabaseType = ''C'')'

Exec('' + @query + '')

------------Build MishapCount resultset------------
SELECT
(
SELECT Count([MishapID])
FROM #tblTemp_Filter_Table
WHERE (((#tblTemp_Filter_Table.MishapID) In (
SELECT DISTINCT #tblTemp_Filter_Table.MishapID
FROM #tblTemp_Filter_Table, tblFactors, tblMishapFactors
WHERE #tblTemp_Filter_Table.MishapID = tblMishapFactors.MishapID_FK AND
tblFactors.[3rdLevelCode] = tblMishapFactors.[3rdLevelCode_FK] AND (
tblFactors.[1stLevelCode] = 'MG'))))) AS MG,
(
SELECT Count([MishapID])
FROM #tblTemp_Filter_Table
WHERE (((#tblTemp_Filter_Table.MishapID) In (
SELECT DISTINCT #tblTemp_Filter_Table.MishapID
FROM #tblTemp_Filter_Table, tblMishapFactors
WHERE #tblTemp_Filter_Table.MishapID = tblMishapFactors.MishapID_FK AND (
tblMishapFactors.[3rdLevelCode_FK] = 'EXC'))))) AS EXC,
(
SELECT Count([#tblTemp_Filter_Table].[MishapID])
FROM #tblTemp_Filter_Table)
AS TotalMishaps;

return

 

[tlbroadbent]

Temporary tables do not exist outside the scope of the Execute statement. Either create the table within the SP (not using Execute) and use the dynamic SQL to insert data. Or use a global temporary table designated with ## rather than #.

SELECT MishapID INTO ##tblTemp_Filter_Table ... Terry L. Broadbent
faq183-874 contains some tips and ideas for posting questions in these forums. Please review it if you have time.

 

[TonyBoex]

Thanks. I'd have run into that brick wall all day. Now I can move on to the next task.