SQL Server Dynamic creation of global temp table and insert data issue

Question

My requirement is to create a global temporary table and store data there which I would access later. I give a dynamic name to my global temporary table and getting error that

Invalid object name '##Tmp1_84'.

84 is @SPID

here is my script. please have a look and tell me what to rectify in code to get rid of runtime error Invalid object name '##Tmp1_84'

CREATE Proc USP_GetValuationValue            
(            
 @Ticker VARCHAR(10),            
 @ClientCode VARCHAR(10),            
 @GroupName VARCHAR(10)            
)            
AS   
  
 DECLARE @SPID VARCHAR(MAX)    
    DECLARE @SQL nvarchar(MAX),          
             @CRLF nchar(2) = NCHAR(13) + NCHAR(10);          
 SELECT @SPID=CAST(@@SPID AS VARCHAR)    
                      
     SET @SQL = N'SELECT * INTO ##Tmp1_'+@SPID+' FROM (SELECT  min(id) ID,f.ticker,f.ClientCode,f.GroupName,f.RecOrder,' + STUFF((SELECT N',' + @CRLF + N'       ' +          
                 N'MAX(CASE FieldName WHEN ' + QUOTENAME(FieldName,'''') + N' THEN FieldValue END) AS ' + QUOTENAME(FieldName)          
         FROM tblValuationSubGroup g            
         WHERE ticker=@Ticker AND ClientCode=@ClientCode AND GroupName=@GroupName          
         GROUP BY FieldName          
         ORDER BY MIN(FieldOrder)          
         FOR XML PATH(''),TYPE).value('(./text())[1]','nvarchar(MAX)'),1,10,N'') + @CRLF +          
     N'FROM (select * from tblValuationFieldValue' + @CRLF +          
     N'WHERE Ticker = '''+@Ticker+'''  AND ClientCode = '''+@ClientCode+''' AND GroupName='''+@GroupName+''') f' + @CRLF +          
     N'GROUP BY f.ticker,f.ClientCode,f.GroupName,f.RecOrder) X';           
                    
 --EXEC sys.sp_executesql @SQL     
    
      
 EXEC(@SQL)    
 EXEC('select * from ##Tmp1_'+@SPID+' ORDER BY Broker')    
 EXEC('DROP TABLE IF EXISTS ##Tmp1_'+@SPID)

Why use 3 dynamic statements and not just one? Why `INSERT` the data into a temporary table when you're just going to `SELECT` from it afterwards? Why **inject** your parameters and not **parametrise** them (this is a huge and fatal flaw). — Thom A, Jun 08 '22 at 09:00
I must admit, with the `@CRLF` and the `(./text())[1]` the above looks like my handiwork... — Thom A, Jun 08 '22 at 09:14

score 0 · Accepted Answer · answered Jun 08 '22 at 09:18

George has mentioned why your attempt doesn't work in their answer, so I'm not going to touch on that.

I'm instead going to fix the problem, which I touch on in my comments. In truth, there is no need for a (global) temporary table, you just SELECT ... INTO it and then SELECT from it; you make no further transformations making it pointless. AS such you could just SELECT the data in the first place, no temporary table needed.

I also fix your injection issue; this is a fatal flaw. Dynamic SQL accepts parameters and you using them is a must; not using parameters and instead using injection opens you up to all sorts of errors and security issues.

I can't test this, but I suspect this will work. If not, use your best friend to debug and propagate fixes to the dynamic SQL on any bits I've missed (or provide code we can run above with an MRE).

CREATE Proc USP_GetValuationValue            
(            
 @Ticker VARCHAR(10),            
 @ClientCode VARCHAR(10),            
 @GroupName VARCHAR(10)            
)            
AS   
BEGIN 
    DECLARE @SPID VARCHAR(MAX), --Is this even used now?
            @SQL nvarchar(MAX),          
            @CRLF nchar(2) = NCHAR(13) + NCHAR(10);          

    SELECT @SPID=CAST(@@SPID AS VARCHAR);    
                         
        SET @SQL = N'SELECT * FROM (SELECT  min(id) ID,f.ticker,f.ClientCode,f.GroupName,f.RecOrder,' + STUFF((SELECT N',' + @CRLF + N'       ' +          
                    N'MAX(CASE FieldName WHEN ' + QUOTENAME(FieldName,'''') + N' THEN FieldValue END) AS ' + QUOTENAME(FieldName)          
            FROM tblValuationSubGroup g            
            WHERE ticker=@Ticker AND ClientCode=@ClientCode AND GroupName=@GroupName          
            GROUP BY FieldName          
            ORDER BY MIN(FieldOrder)          
            FOR XML PATH(''),TYPE).value('(./text())[1]','nvarchar(MAX)'),1,10,N'') + @CRLF +          
        N'FROM (select * from tblValuationFieldValue' + @CRLF +          
        N'WHERE Ticker = @Ticker AND ClientCode = @ClientCode AND GroupName= @GroupName) f' + @CRLF +          
        N'GROUP BY f.ticker,f.ClientCode,f.GroupName,f.RecOrder) X' + @CRLF +
        N'ORDER Y Broker;';           
                       
    --PRINT @SQL; --YOur best friend
    --EXEC sys.sp_executesql @SQL --Why did you comment this out? This is correct!
    EXEC sys.sp_executesql @SQL, N'@Ticker varchar(10), @ClientCode varchar(10), @GroupName varchar(10)', @Ticker, @ClientCode, @GroupName
END;

SQL Server Dynamic creation of global temp table and insert data issue

1 Answers1