SQL Server: How to segregate select and store in variable

Question

See my sql where multiple select exist with in another select. for clarity & cleanness how can separate each sql and store in varchar type variable?

see my sql

 DECLARE @Ticker VARCHAR(10),            
 @ClientCode VARCHAR(10),            
 @GroupName VARCHAR(10)    
  
 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)  

Suppose i want to extract & store this below sql into varchar type variable from main sql

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'')

again want to store this sql into another sql variable

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  

at the end i want to refer those sql1 & sql2 variable in main sql. so please help me to extract those two sql into different varchar type variable and refer those sql in main sql.

Mainly there is 4 select in @alenzi variable. if possible please extract all select and store in separate variable and at end use those variable in main sql.

thanks

Answer 1

Hi @T.Zacks ，

Could you please provide your expected output of @alenzi ?

Per my limited understanding,please refer below and check whether it is working.

DECLARE @Ticker VARCHAR(10),            
@ClientCode VARCHAR(10),            
@GroupName VARCHAR(10)    
      
DECLARE @SPID VARCHAR(MAX)    
DECLARE @SQL nvarchar(MAX),@CRLF nchar(2) = NCHAR(13) + NCHAR(10);   
DECLARE @SQL1 nvarchar(MAX), @SQL2 nvarchar(MAX),@SQL3 nvarchar(MAX)     
SELECT @SPID=CAST(@@SPID AS VARCHAR)          
  
SET @SQL1=  N'SELECT * INTO ##Tmp1_'+@SPID+' FROM (SELECT  min(id) ID,f.ticker,f.ClientCode,f.GroupName,f.RecOrder,'  
  
SET @SQL2=  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'')   
  
SET @SQL3=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';   
  
SET @SQL =@SQL1+@CRLF + @SQL2+@CRLF + @SQL3  
  
--print @SQL   
  EXEC(@SQL)    
  EXEC('select * from ##Tmp1_'+@SPID+' ORDER BY Broker')    
  EXEC('DROP TABLE IF EXISTS ##Tmp1_'+@SPID)  

Best regards,
Melissa

---

If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Answer 2

I am not sure that I understand exactly what you want to do.

However, on my web site I have an article The Curse of Blessings of Dynamic SQL, which discusses several aspects of dynamic SQL, for instance the use parameterised statements, something I can see that you are in urgent need of learning. The article also includes a style guide where you can several tricks to write dynamic SQL in a more structured way so that the code is easier to read and maintain.