0

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
Jessica Warren
  • 378
  • 1
  • 4
  • 15
  • "Running a report" means what exactly? Common usage is to package this logic into a stored procedure that generates the desired resultset - no permanent table required. But any guidance will require knowledge of how this code interacts with your "report tool". – SMor Sep 27 '20 at 16:51
  • @SMor so the code is in a stored procedure. The report is spit on from the +table created but i have that logic figured out. How would you throw sql and sql1 scripts together to not use temp tables? – Jessica Warren Sep 27 '20 at 16:55
  • The above SQL looks **very** open to injection attacks with syntax like `'...from '+@ClientTable+'...'` and `'...isnull('+Seq+',9999)...'` You ***need*** to properly quote and parametrise your dynamic statements. – Thom A Sep 27 '20 at 17:08
  • What are you actually trying to achieve with the above in the first place? Why do you "need" dynamic SQL? Also, include the **full** DLL for your procedure. Many of the variables in the above statements, such as `@ClientTable` and `@table` aren't declared in your statement; where are their values coming from? – Thom A Sep 27 '20 at 17:21

0 Answers0