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

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

Open in new window

Who is Participating?
chapmandewConnect With a Mentor Commented:
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'
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.
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.

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.

All Courses

From novice to tech pro — start learning today.