question

TZacks-2728 avatar image
0 Votes"
TZacks-2728 asked MelissaMa-msft answered

SQL Server: How to segregate select and store in variable

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 @SQL variable. if possible please extract all select and store in separate variable and at end use those variable in main sql.

thanks

sql-server-transact-sql
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

MelissaMa-msft avatar image
1 Vote"
MelissaMa-msft answered

Hi @TZacks-2728

Could you please provide your expected output of @sql?

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.

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

ErlandSommarskog avatar image
1 Vote"
ErlandSommarskog answered

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.

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.