[2 days left] What’s wrong with your cloud strategy? Learn why multicloud solutions matter with Nimble Storage.Register Now

x
?
Solved

Invalid object name error when referencing a temporary table

Posted on 2008-09-29
3
Medium Priority
?
1,272 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
3 Comments
 
LVL 60

Accepted Solution

by:
chapmandew earned 2000 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

On Demand Webinar: Networking for the Cloud Era

Did you know SD-WANs can improve network connectivity? Check out this webinar to learn how an SD-WAN simplified, one-click tool can help you migrate and manage data in the cloud.

Question has a verified solution.

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

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.
One of the most important things in an application is the query performance. This article intends to give you good tips to improve the performance of your queries.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

649 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