1

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?

6

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

1
  • 1
    @IvanGusev Note the reasoning for this is because a CTE (the WITH clause) needs to be the first in the batch (per the SQL Server spec). Normally you can prefix the WITH clause with a semicolon ; to terminate the previous batch, but in your case that wouldn't work with syntax you were trying to use. Instead this answer re-writes your syntax so that the variable is declared first and that batch is terminated with a semicolon, then the CTE is defined and finally the variable is set to the result of the CTE and selected.
    – J.D.
    Mar 17 at 21:15

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.