• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1351
  • Last Modified:

Invalid object name error when referencing a temporary table

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
Samm1502
Asked:
Samm1502
1 Solution
 
chapmandewCommented:
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
 
BrandonGalderisiCommented:
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
 
Samm1502Author Commented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: Amazon Web Services - Basic

Are you thinking about creating an Amazon Web Services account for your business? Not sure where to start? In this course you’ll get an overview of the history of AWS and take a tour of their user interface.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now