DECLARE @Cname AS VARCHAR(8) = 'MyHeader'; --; format( dateadd(Month, -0, @mydate), 'yyyyMM') ;
declare @query AS NVARCHAR(MAX) = ' Select getdate() as ' + @cname
declare @query2 AS NVARCHAR(MAX) = ' Select WithClm as ' + @cname+ ' from WithQry '
--select (@query) --prints the @query
--execute (@query) --This one works ok
;with WithQry as (
select getdate() as WithClm
--select * from WithQry --- This one works fine
--select * from WithQry where 1=2 --dummy select
--select (@query2); -- = Select WithClm as MyHeader from WithQry
--the select works if I have a dummy select above
-- Select WithClm as MyHeader from WithQry = query 2
execute (@query2); --Incorrect syntax near the keyword 'execute'.
--Possible to execute @query2 ? ( This is a simplified sample, in real life I have 2-3 with statements, and some use of pivot, in theory they can be replaced with some nested views, but I hope not