如何将CTE-JSON结果应用于变量 [英] How to apply CTE-JSON result to variable

问题描述

我有一个这样的简单CTE：

 DECLARE @Json nvarchar（MAX）; 
 WITH cteBuildJSON（Sequence，level，WBS，parentId）AS（
 SELECT anchor.Sequence，0 as level，Cast（mnu_order As NVARCHAR（512））AS WBS，
 mnu_IDParentSequence as parentId 
来自tblMenuDefinition作为锚
 WHERE mnu_IDParentSequence为null并且mnu_State = 0并且mnu_IDLicenseSequence为null 
 
 UNION ALL 
 
选择recur.Sequence，cte.level + 1，Cast（cte.WBS +'。'+ Cast（recur.mnu_order As NVARCHAR（512））As NVARCHAR（512））AS WBS，
 recur.mnu_IDParentSequence as parentId 
 FROM tblMenuDefinition AS recur 
 INNER JOIN cteBuildJSON as cte On cte.Sequence = recur.mnu_IDParentSequence 
 where recur.mnu_State = 0）
 SELECT * 
 FROM cteBuildJSON 
按WBS排序
对于JSON路径

如何将结果应用于声明的可变量@Json？

谢谢，

问候，

Arno

决策案

 WITH cteBuildJSON（Sequence，level，WBS，parentId）AS（
 SELECT anchor.Sequence，0 as level，Cast（mnu_order As NVARCHAR（512） ））AS WBS，
 mnu_IDParentSequence作为parentId 
来自tblMenuDefinition作为锚
 WHERE mnu_IDParentSequence为null并且mnu_State = 0并且mnu_IDLicenseSequence为null 
 
 UNION ALL 
 
选择recur.Sequence，cte.level + 1，Cast（cte.WBS +'。'+ Cast（recur.mnu_order As NVARCHAR（512））作为NVARCHAR（512））AS WBS，
 recur .mnu_IDParentSequence作为parentId 
 FROM tblMenuDefinition AS recur 
 INNER JOIN cteBuildJSON as cte On cte.Sequence = recur.mnu_IDParentSequence 
 where recur.mnu_State = 0）
 SELECT @Json =（
 SELECT * 
来自cteBuildJSON 
按WBS排序）
对于JSON路径

I have a simple CTE like this:

DECLARE @Json nvarchar(MAX);
	WITH cteBuildJSON(Sequence,level,WBS,parentId) AS ( 	
		SELECT	anchor.Sequence,0 as level, Cast(mnu_order As NVARCHAR(512)) AS WBS, 			
				mnu_IDParentSequence As parentId 	
		From	tblMenuDefinition As anchor 	
		WHERE	mnu_IDParentSequence Is null And mnu_State=0 And mnu_IDLicenseSequence Is null 	
		
		UNION ALL  	
		
		Select	recur.Sequence, cte.level + 1,	Cast(cte.WBS + '.' + Cast(recur.mnu_order As NVARCHAR(512)) As NVARCHAR(512)) AS WBS, 			
				recur.mnu_IDParentSequence As parentId 	
		FROM	tblMenuDefinition AS recur 			
				INNER JOIN cteBuildJSON As cte On cte.Sequence = recur.mnu_IDParentSequence 	
		Where	recur.mnu_State=0 )
	SELECT	*  
	FROM	cteBuildJSON
	Order By WBS
	For JSON Path

How can I apply th result to the declared varable @Json?

Thanks,

Regards,

Arno

解决方案

WITH cteBuildJSON(Sequence,level,WBS,parentId) AS ( 	
		SELECT	anchor.Sequence,0 as level, Cast(mnu_order As NVARCHAR(512)) AS WBS, 			
				mnu_IDParentSequence As parentId 	
		From	tblMenuDefinition As anchor 	
		WHERE	mnu_IDParentSequence Is null And mnu_State=0 And mnu_IDLicenseSequence Is null 	
		
		UNION ALL  	
		
		Select	recur.Sequence, cte.level + 1,	Cast(cte.WBS + '.' + Cast(recur.mnu_order As NVARCHAR(512)) As NVARCHAR(512)) AS WBS, 			
				recur.mnu_IDParentSequence As parentId 	
		FROM	tblMenuDefinition AS recur 			
				INNER JOIN cteBuildJSON As cte On cte.Sequence = recur.mnu_IDParentSequence 	
		Where	recur.mnu_State=0 )
SELECT @Json = (	
        SELECT	*  
	FROM	cteBuildJSON
	Order By WBS)
	For JSON Path

这篇关于如何将CTE-JSON结果应用于变量的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！