Execute after a With statement

Transact-SQL
1

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

2

WITH is part of specific SQL statements: SELECT, DELETE, INSERT, UPDATE. It's not dynamic or generic to be used with any SQL command.

3 
declare @query2 AS NVARCHAR(MAX) = ' Select WithClm as ' + @cname+ ' from WithQry '

;with WithQry as (
select getdate() as WithClm
)
--you have unfinished business here after 
--you build your cte there needs to be some code

execute (@query2); --Incorrect syntax near the keyword 'execute'.

--You want to do this
declare @Cname AS VARCHAR(8) = 'MyHeader'; 
--declare @query2 AS NVARCHAR(MAX) = ' Select WithClm as ' + @cname+ ' from WithQry '

declare @query2 NVARCHAR(MAX) = 
';with WithQry as (
		select getdate() as WithClm
		)
Select WithClm as ' + @cname + ' from WithQry '


execute (@query2);
4

Thanks, I think this is something i can use. I will try
Am I then limitied to one block of With ?

5

declare @Cname AS VARCHAR(8) = 'MyHeader';

declare @query1 NVARCHAR(MAX) =
';with WithQry1 as (
select 1000 as WithClm
)'

declare @query2 NVARCHAR(MAX) =
', WithQry as (
select WithClm from WithQry1
)
Select WithClm as ' + @cname + ' from WithQry ; '

execute (@query1 + @query2); --wow fantastic !!

6

yeah its cool and all but why do yoilu want to do dynamic sql