Solved

Invalid object name error when referencing a temporary table

Posted on 2008-09-29
3
1,252 Views
Last Modified: 2013-11-05
I am struggling to create and use a temporary table in my stored procedure and cannot understand what is happening.

Within the procedure my SQL is shown in the first code snippet below.  The value in @sSQL is:

SELECT IDENTITY(int, 1, 1) 'RowNum', [Ref] 'Reference', [NINum] 'Column1' , [Nam] 'Column2' , [Surnam] 'Column3' , [MemNum] 'Column4'  INTO #TempResults FROM [dbo].[MyTable]  WHERE [NINum]='AB123456D' AND [Nam]='My Member 1' AND [Surname]='Member 1' AND [MemNo]='311'

When I execute this code there is no error returned and the value of TotalRows when printed out is 2 which is correct.

HOWEVER as soon as I try to select the contents of the temp table I should just have created i get the Invalid Object Name '#TempResults#' error???

If i execute the above statement directly i.e. do not build the string @sSQL and the call sp_Executesql to execute it indirectly it all works great and I have my temporary table.

Unfortunately I need to dynimacally build the SQL so then have to call sp_Executesql so how do I insert my data into a temporary table via this call so that I can then use it afterwards???

Many thanks
Sam


SELECT @sSQL = @sSQL + @SQLChangedSince
	
	EXEC sp_Executesql @sSQL
	SELECT @nErr = @@Error, @nCnt = @@Rowcount
	IF(@nErr <> 0)
	BEGIN
		SELECT @nRetVal = 15
		GOTO ERR_HANDLER
	END
	SELECT @TotalRows = @nCnt
	print 'total rows is: ' + convert(varchar(10), @TotalRows)
	select * from #TempResults

Open in new window

0
Comment
Question by:Samm1502
3 Comments
 
LVL 60

Accepted Solution

by:
chapmandew earned 500 total points
ID: 22596254
The temp table is outside of the scope of your SELECt statement, as it is created inside sp_executesql.  To get around it, you could use a global temp table instead:

SELECT IDENTITY(int, 1, 1) 'RowNum', [Ref] 'Reference', [NINum] 'Column1' , [Nam] 'Column2' , [Surnam] 'Column3' , [MemNum] 'Column4'  INTO ##TempResults FROM [dbo].[MyTable]  WHERE [NINum]='AB123456D' AND [Nam]='My Member 1' AND [Surname]='Member 1' AND [MemNo]='311'
0
 
LVL 39

Expert Comment

by:BrandonGalderisi
ID: 22596268
If you are building it in @sSQL, then the temporary table goes out of scope immediately once sp_executeSQL closes.  You would need to create #TempResults before executing @sSQL, and change it to an insert into #Tempresults, instead of select into #tempResults.
0
 

Author Closing Comment

by:Samm1502
ID: 31501175
Yes will do that as I don't know how many columns I will have beforehand so can't create the table up front.

Cheers
Sam
0

Featured Post

NAS Cloud Backup Strategies

This article explains backup scenarios when using network storage. We review the so-called “3-2-1 strategy” and summarize the methods you can use to send NAS data to the cloud

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
Via a live example, show how to shrink a transaction log file down to a reasonable size.

828 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question