Sql-server – put the result of a query into a variable and WITH statement

sql servert-sql

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?

Best Answer

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<>fiddle here

Related Question