无法将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。

关键字 "with" 附近有语法错误。如果这个语句是一个常用表达式、xmlnamespaces 子句或更改跟踪上下文子句，则前面的语句必须以分号结束。

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

- ca9163d9

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>

- Mikael Eriksson

Answer 2

我认为问题在于你尝试赋值的方式。请尝试使用以下方法：

declare @s varchar(max);
with temp as
(
   select ....
  from ... join c on .... join c on .... join c on ....
  for xml raw('...'), elements
)
select @s = value from temp
select @s

正如错误信息所述，您的真正问题是使用CTE时前面的语句没有以;结尾，这是必须的要求。

我使用select 'test' as value代替您的查询来定义CTE并运行它，结果符合预期。

- Abe Miessler

---

for xml 可能会使其不可行？ - ca9163d9

我不确定。目前我无法测试它。我建议尝试上面提供的方法。你遇到的错误似乎与使用“for xml”无关... - Abe Miessler

当CTE在批处理语句中使用时，它之前的语句必须加上分号。 - HABO

需要将 with temp as (select ... for xml...) 更改为 with c as (...), temp(value) as (...)。 - ca9163d9

有人能解释一下为什么 SELECT @s = 可以工作，但 SET @s = 不行吗？ - rstackhouse

Answer 3

好的，我明白了。无法在一个声明和初始化语句中完成。

declare @s varchar(max);

with c as (select 1 a union all select 2 union all select 3)
, x(s) as (select a from c order by a desc for xml raw('tr'), elements)
select @s = s from x

- ca9163d9

---

@Abe - “独创性的秘密是忘记来源”+1 给你的答案。 - Scotty.NET