put the result of a query into a variable and WITH statement I need to put the result of a query into a variable.
Just a query, works as successful
DECLARE @count INT = (

SELECT count (*)
FROM [AdventureWorks].[Person].[Address]    
);

select @count;

But if I need to use the WITH statement in a query, then I get a syntax error
DECLARE @count INT = (
   WITH person_address (id)
   as (
    SELECT AddressID
    FROM [AdventureWorks].[Person].[Address]
   )
   SELECT count (*)
   FROM person_address    
);

select @count;


Msg 156, Level 15, State 1, Line 2
Incorrect syntax near the keyword 'WITH'.


Msg 319, Level 15, State 1, Line 2
Incorrect syntax near the keyword 'with'. If this statement is a common table expression, an xmlnamespaces clause or a change tracking context clause, the previous statement must be terminated with a semicolon.


Msg 102, Level 15, State 1, Line 9
Incorrect syntax near ')'.

How do I put the query value into a variable if the WITH clause is used in the SQL statement?
Assign the variable in the last query.
DECLARE @count INT;

WITH person_address (id)
as (
    SELECT AddressID
    FROM [AdventureWorks].[Person].[Address]
   )
   SELECT @count = count (*)
   FROM person_address;

select @count;

create table t (id int);

insert into t values
(1),(1),(2),(1),(2);

declare @cnt int;

with ct as
(
    select id from t
)
select
  @cnt = count(*)
from
  ct
where id = 1;

select @cnt;
 



| (No column name) |
| ---------------: |
|                3 |


db&lt;&gt;fiddle here