Temporary tables in Stored procedure are not accesing data?

Question

This is My code

create proc TEMP
    AS
    BEGIN
    DECLARE @SQL nvarchar(4000)
    IF OBJECT_ID(N'tempdb..#TEMP1') IS NOT NULL
        DROP TABLE #TEMP1;
    SET @SQL ='SELECT CUSTOMERS,AREA,HOUSEHOLDS'+CHAR(10)
    SET @SQL = @SQL +'INTO #TEMP1'+CHAR(10)
    SET @SQL = @SQL +'FROM NEW'+CHAR(10)
    PRINT(@SQL)
    EXEC (@SQL)
    IF OBJECT_ID(N'tempdb..#TEMP2') IS NOT NULL
        DROP TABLE #TEMP2;
    SET @SQL ='SELECT CUSTOMERS,AREA,VEHICELS'+CHAR(10)
    SET @SQL = @SQL +'INTO #TEMP2'+CHAR(10)
    SET @SQL = @SQL +'FROM OLD'+CHAR(10)
    PRINT(@SQL)
    EXEC (@SQL)
    IF OBJECT_ID(N'tempdb..#TEMP3') IS NOT NULL
        DROP TABLE #TEMP3;
    SET @SQL ='SELECT 0.VEHICELS,C.HOUSEHOLDS'+CHAR(10)
    SET @SQL = @SQL +'INTO #TEMP3'+CHAR(10)
    SET @SQL = @SQL +'FROM #TEMP1 C'+CHAR(10)
    SET @SQL = @SQL +'INNER JOIN #TEMP2 O '+CHAR(10)
    SET @SQL = @SQL +'on C.CUSTOMERS=O.CUSTOMERS'+CHAR(10)
    SET @SQL = @SQL +'AND C.AREA=O.AREA'+CHAR(10)
    PRINT(@SQL)
    EXEC (@SQL)
    END

My error:

Msg 208, Level 16, State 0, Line 1
Invalid object name '#TEMP1'.

First TEMP1 and TEMP2 are working well, but when it comes to TEMP3 it doesn't access TEMP1 where the data is present

Can you guys check this confusing problem ???

DECLARE @sql varchar(max)
SET @sql = 'CREATE TABLE ##T1 (Col1 varchar(20))'
EXEC(@sql)
INSERT INTO ##T1 (Col1) VALUES ('This will work.')
SELECT * FROM ##T1

DO WE GET ANY PROBLEMS IN FUTURE IF WE USE GLOBALTEMPORARY TABLES???

I'm not sure why you're doing this as dynamic SQL and using `EXEC`. `EXEC` creates a new scope for executing the provided query. When a scope exits, any temp tables created within that scope are dropped. — Damien_The_Unbeliever, Aug 22 '13 at 13:34
Why do you use dynamic SQL? If you use a normal SQL your temp tables are visibles in all execution of your SP, so you can populate temp3 with temp1 and temp2. — Joe Taras, Aug 23 '13 at 07:57

score 0 · Answer 1 · answered Aug 22 '13 at 13:44

once the exec finishes executing the #temp table is deleted maybe if you try using a wider scope, it could succeed, I don't know.

try something like this.

create proc TEMP
AS
BEGIN
    DECLARE @now datetime;
    DECLARE @TempTableTableSuffix sysname
    SET @now = GETDATE()

    select @TempTableTableSuffix = CONVERT(VARCHAR, CONVERT(int,RAND(DATEPART(MILLISECOND,@now)+1000*(DATEPART(SECOND,@now)+60*(DATEPART(MINUTE,@now)+60*DATEPART(HOUR,@now)))) * 100000000))

    DECLARE @SQL nvarchar(4000)
    SET @SQL ='SELECT CUSTOMERS,AREA,HOUSEHOLDS'+CHAR(10)
    SET @SQL = @SQL +'INTO ##TEMP1' + @TempTableTableSuffix + CHAR(10)
    SET @SQL = @SQL +'FROM NEW'+CHAR(10)
    PRINT(@SQL)
    EXEC (@SQL)
    SET @SQL ='SELECT CUSTOMERS,AREA,VEHICELS'+CHAR(10)
    SET @SQL = @SQL +'INTO ##TEMP2' + @TempTableTableSuffix + CHAR(10)
    SET @SQL = @SQL +'FROM OLD'+CHAR(10)
    PRINT(@SQL)
    EXEC (@SQL)
    SET @SQL ='SELECT 0.VEHICELS,C.HOUSEHOLDS'+CHAR(10)
    SET @SQL = @SQL +'INTO ##TEMP3'+CHAR(10)
    SET @SQL = @SQL +'FROM ##TEMP1' + + @TempTableTableSuffix + '  C'+CHAR(10)
    SET @SQL = @SQL +'INNER JOIN #TEMP2'+ @TempTableTableSuffix  + ' O '+CHAR(10)
    SET @SQL = @SQL +'on C.CUSTOMERS=O.CUSTOMERS'+CHAR(10)
    SET @SQL = @SQL +'AND C.AREA=O.AREA'+CHAR(10)
    PRINT(@SQL)
    EXEC (@SQL)

END

anyway, I think you don't need dynamic SQL for this.

Global varaible also not working..Invalid object name '##TEMP1'. — GreatLakes07, Aug 22 '13 at 14:01

score 0 · Answer 2 · edited Aug 22 '13 at 14:32

Use single EXEC -

DECLARE @SQL nvarchar(MAX)
IF OBJECT_ID(N'tempdb..#TEMP1') IS NOT NULL
    DROP TABLE #TEMP1;
IF OBJECT_ID(N'tempdb..#TEMP2') IS NOT NULL
    DROP TABLE #TEMP2;    
IF OBJECT_ID(N'tempdb..#TEMP3') IS NOT NULL
    DROP TABLE #TEMP3;        


SET @SQL = ' SELECT CUSTOMERS,AREA,HOUSEHOLDS'+CHAR(10)
SET @SQL = @SQL + ' INTO #TEMP1'+CHAR(10)
SET @SQL = @SQL + ' FROM NEW'+CHAR(10)

SET @SQL = @SQL + ' SELECT CUSTOMERS,AREA,VEHICELS'+CHAR(10)
SET @SQL = @SQL + ' INTO #TEMP2'+CHAR(10)
SET @SQL = @SQL + ' FROM OLD'+CHAR(10)
SET @SQL = @SQL + ' SELECT 0.VEHICELS,C.HOUSEHOLDS'+CHAR(10)
SET @SQL = @SQL + ' INTO #TEMP3'+CHAR(10)
SET @SQL = @SQL + ' FROM #TEMP1 C'+CHAR(10)
SET @SQL = @SQL + ' INNER JOIN #TEMP2 O '+CHAR(10)
SET @SQL = @SQL + ' on C.CUSTOMERS=O.CUSTOMERS'+CHAR(10)
SET @SQL = @SQL + ' AND C.AREA=O.AREA'+CHAR(10)
PRINT(@SQL)
EXEC (@SQL)

The right way to show code here on StackOverflow is to indent every line by four spaces (or just highlight the block and hit the `{}` button and the editor will do it for you). `
` blocks sort of work, but don't turn on the syntax highlighting. — Damien_The_Unbeliever, Aug 22 '13 at 14:32

Temporary tables in Stored procedure are not accesing data?

2 Answers2