Write a table driven query with a dynamically declared table name?

Question

I am consistently running a report and creating tables for this report. Now other users are running thsi report as well. So I need users to be able to run stored procedure simultaniously without worry of overwriting tables. I tried using a simple temp table but I need the temporary table to work through out two "functions." One dynamic sql statement that creates a table and one dynamic sql statment thats table driven.

My primary issue is I want the table driven piece of code to be able to see the global temporary table variable but it does not. Is there a work around for this while still using temporary tables? is there a way to run both dynamic sql statements at once so the other type of temp table would work?

Any advice in the right direction is helpful. Thank you.

DECLARE @TmpGlobalTable varchar(255) = 'SomeText_' + convert(varchar(36),NEWID())

SELECT @SQL =   @SQL +'


SELECT IDENTITY(INT) as idcol, date, Desc As [Description]
INTO [##' + @TmpGlobalTable + '] 
FROM dbo.appendix 
WHERE RecordStatus = 1
 and casestatement from user input 
 
 
 '
print(@sql) 
exec(@sql) 



Declare @sql1 varchar(max) = ''
SELECT @SQL1 =  @SQL1 +'

              insert into dbo.'+@table+'
              select ''1'', '''+date+''' as Sequence, Description as Description_color, buyer, seller, price, option
              from '+@ClientTable+'
              where isnull('+Seq+',9999) <= cutoffvalue
              group by description , buyer, seller, price, option
     '
     from 
     [##' + @TmpGlobalTable + '] 
                
             

print(@sql1) 
exec(@sql1) 

EXEC ('DROP TABLE [##' + @TmpGlobalTable + ']')
PRINT 'Dropped Table ' + @TmpGlobalTable