sql - 无法将 CTE 结果分配给 varchar 变量？

Question

我正在尝试编写以下 SQL。

declare @s varchar(max) = (
  with c as (select ...)
  select a, b, c, d, ....
  from ... join c on .... join c on .... join c on ....
  order by ...
  for xml raw('...'), elements
);

但是，这是错误的语法(下面显示了错误消息)。我必须将其转换为子查询吗？我试图避免在多个地方扩展 CTE。

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.

更新:
for xml 和 order by 使 select @s = ...

Answer 1

您需要将@s 的声明与赋值分开。

这样的东西对你有用。

declare @T table
(
  ID int,
  Col1 int
)

insert into @T values(1, 10),(2, 20)

declare @s varchar(max)

;with C as
(
  select *
  from @T
)
select @s = 
  (
    select *
    from C as C1
      inner join C as C2
        on C1.ID = C2.ID
    for xml raw, elements
  )

select @s

结果:

<row>
  <ID>1</ID>
  <Col1>10</Col1>
  <ID>1</ID>
  <Col1>10</Col1>
</row>
<row>
  <ID>2</ID>
  <Col1>20</Col1>
  <ID>2</ID>
  <Col1>20</Col1>
</row>