如何在T-SQL中用CTE结合FOR JSON生成JSON并存储到局部变量？

你遇到的问题其实是因为CTE需要和它关联的SELECT语句作为一个完整的查询单元，只要调整语法结构，把CTE定义和后续查询都放在赋值操作的括号内部就能解决。

正确实现示例 #

DECLARE @var NVARCHAR(MAX) = (
    WITH y AS (
        SELECT Val FROM (VALUES (1)) AS _(Val)
    )
    SELECT x.Val, y.Val ValY 
    FROM (VALUES (1)) AS x(Val) 
    JOIN y ON y.Val = x.Val 
    FOR JSON AUTO
)

-- 验证生成的JSON结果
SELECT @var

为什么原来的写法不生效？ #

你之前尝试把整个CTE语句用DECLARE @var = (...)包裹时，大概率是错误地将CTE定义放在了括号外面，导致SQL引擎无法识别CTE和后续SELECT的关联关系。CTE的作用域仅限于紧跟它的第一个查询语句，所以必须把CTE和使用它的查询放在同一个逻辑块里。

另一种更直观的写法 #

如果你更喜欢拆分逻辑，也可以先定义CTE，再通过SELECT直接赋值给变量（FOR JSON会返回单个字符串值，适合直接赋值）：

DECLARE @var NVARCHAR(MAX)

WITH y AS (
    SELECT Val FROM (VALUES (1)) AS _(Val)
)
SELECT @var = (
    SELECT x.Val, y.Val ValY 
    FROM (VALUES (1)) AS x(Val) 
    JOIN y ON y.Val = x.Val 
    FOR JSON AUTO
)

SELECT @var

两种写法都能正常工作，第一种更紧凑，第二种可读性更强，你可以根据自己的习惯选择。

内容的提问来源于stack exchange，提问作者Glia