Solved

Invalid object name error when referencing a temporary table

Posted on 2008-09-29
3
1,251 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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

Suggested Solutions

This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

810 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