Samm1502
asked on
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
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
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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.
ASKER
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
Cheers
Sam