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?