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???
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