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