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?
