存储过程解析表菜单并输出JSON格式字符串

表结构如下：

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[T_4s_Menu](
	[F_MenuID] [int] IDENTITY(1,1) NOT NULL,
	[F_MenuName] [nvarchar](50) NULL,
	[F_MenuUrl] [varchar](100) NULL,
	[F_ParentID] [int] NULL,
	[F_MenuCode] [varchar](10) NULL,
	[F_Order] [int] NULL,
	[F_IsMenu] [bit] NULL,
	[F_IsWindow] [bit] NULL,
 CONSTRAINT [PK_T_4s_Menu] PRIMARY KEY CLUSTERED 
(
	[F_MenuID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO

存储过程如下：

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:	<CSDN_5653325>
-- Create date: <20130729>
-- Description:	<Admin_Menu转为JSON类型>
-- =============================================
ALTER PROCEDURE [dbo].[pr_4sMenu2JSON] (@i_AdminID int,@i_GroupID int,@s_ReturnJson varchar(5000) OUTPUT)	 
AS
BEGIN
	SET NOCOUNT ON;
	DECLARE @JSON varchar(5000);--要输出的JSON字符串
	
	DECLARE @root_MenuCount int;--根菜单数量	
	DECLARE @root_MenuID int;--根菜单ID
	DECLARE @root_MenuName nvarchar(50);--根菜单名称
	DECLARE @root_MenuCode varchar(10);--根菜单编码
	
	DECLARE @Return_Rules varchar(3000);--返回查询的权限	
	
	IF(@i_AdminID=-1 AND @i_GroupID=-1)	  --全部
		BEGIN
			SELECT @Return_Rules=',"Rules":"Null"';
		END
	ELSE
		BEGIN
			IF(@i_AdminID>-1)--指定4SID
				BEGIN
					SELECT @Return_Rules=',{'+[F_Rules]+'}' FROM [T_4s] WHERE [F_ID]=@i_AdminID
				END
			ELSE		     --指定用户ID
				BEGIN
					SELECT @Return_Rules=',{'+[F_UserPower]+'}' FROM [T_4s_Users] WHERE [ID]=@i_GroupID
				END	 	    
		END
	 
	SELECT @root_MenuCount=COUNT(0) FROM T_4s_Menu WHERE F_ParentID=1 AND F_MenuID<>1 
	SELECT @JSON='[{"count":'+CONVERT(varchar(10),@root_MenuCount)+',"list":[';
	DECLARE @i int=1;--根菜单循环计数器，用来判断是不是读取最后一条记录
	
	DECLARE cur_Root CURSOR FOR 
		SELECT [F_MenuID],[F_MenuName],[F_MenuCode] FROM T_4s_Menu WHERE F_ParentID=1 AND F_MenuID<>1 order by F_Order asc
	OPEN cur_Root
	FETCH NEXT FROM cur_Root INTO @root_MenuID, @root_MenuName,@root_MenuCode
	WHILE @@FETCH_STATUS = 0
		BEGIN					
			DECLARE @MenuID int;--子菜单ID
			DECLARE @MenuName nvarchar(50);--子菜单名称
			DECLARE @MenuCode varchar(10);--子菜单编码
			DECLARE @MenuUrl varchar(100);--子菜单URL
			DECLARE @IsMenu bit;	--子菜单是否显示
			DECLARE @MenuCount int;--子菜单数量
			DECLARE @j int=1;--子菜单循环计数器，用来判断是否是最后一条记录	
			
			SELECT @MenuCount=COUNT(0) FROM T_4s_Menu WHERE F_ParentID=@root_MenuID
			DECLARE cur_Menu CURSOR FOR
			SELECT [F_MenuID],[F_MenuName],[F_MenuCode],[F_MenuUrl],[F_IsMenu] FROM T_4s_Menu WHERE F_ParentID=@root_MenuID order by F_Order asc
			OPEN cur_Menu
			FETCH NEXT FROM cur_Menu INTO @MenuID, @MenuName,@MenuCode,@MenuUrl,@IsMenu
			
			IF @i=@root_MenuCount  --最后一行数据
				BEGIN
					SELECT @JSON+='{"MenuID":'+CONVERT(varchar(10),@root_MenuID)+',"MenuName":"'+@root_MenuName+'","MenuCode":"'+@root_MenuCode+'","MenuCount":'+CONVERT(varchar(10),@MenuCount)+',"MenuList":['

					WHILE @@FETCH_STATUS = 0
						BEGIN
							IF @j=@MenuCount--最后一条记录
								BEGIN
									SELECT @JSON+='{"MenuID":'+CONVERT(varchar(10),@MenuID)+',"MenuName":"'+@MenuName+'","MenuCode":"'+@MenuCode+'","MenuUrl":"'+@MenuUrl+'","IsMenu":"'+convert(varchar(8),@IsMenu)+'"'
									SELECT @JSON+='}]'										 					
								END	 
							ELSE
								BEGIN
									SELECT @JSON+='{"MenuID":'+CONVERT(varchar(10),@MenuID)+',"MenuName":"'+@MenuName+'","MenuCode":"'+@MenuCode+'","MenuUrl":"'+@MenuUrl+'","IsMenu":"'+convert(varchar(8),@IsMenu)+'"'
									SELECT @JSON+='},'
									SELECT @j+=1;	  									
								END
							FETCH NEXT FROM cur_Menu INTO @MenuID, @MenuName,@MenuCode,@MenuUrl,@IsMenu	
						END		  					
					
					SELECT @JSON+='}'
				END
			ELSE
				BEGIN
					SELECT @JSON+='{"MenuID":'+CONVERT(varchar(10),@root_MenuID)+',"MenuName":"'+@root_MenuName+'","MenuCode":"'+@root_MenuCode+'","MenuCount":'+CONVERT(varchar(10),@MenuCount)+',"MenuList":['

					WHILE @@FETCH_STATUS = 0
						BEGIN
							IF @j=@MenuCount--最后一条记录
								BEGIN
									SELECT @JSON+='{"MenuID":'+CONVERT(varchar(10),@MenuID)+',"MenuName":"'+@MenuName+'","MenuCode":"'+@MenuCode+'","MenuUrl":"'+@MenuUrl+'","IsMenu":"'+convert(varchar(8),@IsMenu)+'"'
									SELECT @JSON+='}]'				 					
								END	 
							ELSE
								BEGIN
									SELECT @JSON+='{"MenuID":'+CONVERT(varchar(10),@MenuID)+',"MenuName":"'+@MenuName+'","MenuCode":"'+@MenuCode+'","MenuUrl":"'+@MenuUrl+'","IsMenu":"'+convert(varchar(8),@IsMenu)+'"'
									SELECT @JSON+='},'
									SELECT @j+=1;	  									
								END
							FETCH NEXT FROM cur_Menu INTO @MenuID, @MenuName,@MenuCode,@MenuUrl ,@IsMenu	
						END
			
					SELECT @JSON+='},'
					SELECT @i+=1;
				END
				 
			CLOSE cur_Menu
			DEALLOCATE cur_Menu
			FETCH NEXT FROM cur_Root INTO @root_MenuID, @root_MenuName,@root_MenuCode
		END 
		
	CLOSE cur_Root
	DEALLOCATE cur_Root
	SELECT @JSON+=']'+@Return_Rules+'}]';
	SELECT @s_ReturnJson= @JSON	
END

结果如下：

[{"count":7,"list":[{"MenuID":2,"MenuName":"信息管理","MenuCode":"NS","MenuCount":2,"MenuList":[{"MenuID":3,"MenuName":"信息发布","MenuCode":"NS_Edit","MenuUrl":"Manage/News/NewsEdit.aspx","IsMenu":"1"},{"MenuID":17,"MenuName":"新闻列表","MenuCode":"NS_NL","MenuUrl":"Manage/News/NewsList.aspx","IsMenu":"1"}]},{"MenuID":4,"MenuName":"新车管理","MenuCode":"4C","MenuCount":2,"MenuList":[{"MenuID":5,"MenuName":"车辆报价","MenuCode":"4C_PC","MenuUrl":"Manage/Cars/CarPrice.aspx","IsMenu":"1"},{"MenuID":16,"MenuName":"所售车型","MenuCode":"4C_CL","MenuUrl":"Manage/Cars/SellCarList.aspx","IsMenu":"1"}]},{"MenuID":6,"MenuName":"留言咨询","MenuCode":"CM","MenuCount":4,"MenuList":[{"MenuID":7,"MenuName":"试乘试驾","MenuCode":"CM_App","MenuUrl":"Manage/Application/ApplicationList.aspx","IsMenu":"1"},{"MenuID":8,"MenuName":"底价咨询","MenuCode":"CM_zxdj","MenuUrl":"Manage/Comment/zxdj.aspx","IsMenu":"0"},{"MenuID":9,"MenuName":"客户留言","MenuCode":"CM_leave","MenuUrl":"Manage/LeaveMessage/LeaveMessage_List.aspx","IsMenu":"1"},{"MenuID":20,"MenuName":"站内消息","MenuCode":"CM_Mes","MenuUrl":"Manage/Message/Message_List.aspx","IsMenu":"0"}]},{"MenuID":26,"MenuName":"二手车管理","MenuCode":"2S","MenuCount":8,"MenuList":[{"MenuID":31,"MenuName":"二手车列表","MenuCode":"2S_List","MenuUrl":"Manage/2Shou/2CarList.aspx","IsMenu":"1"},{"MenuID":30,"MenuName":"二手车添加","MenuCode":"2S_ADD","MenuUrl":"Manage/2Shou/2CarAdd.aspx","IsMenu":"1"},{"MenuID":28,"MenuName":"已售车辆","MenuCode":"2S_Selled","MenuUrl":"Manage/2Shou/selled.aspx","IsMenu":"0"},{"MenuID":27,"MenuName":"竞价平台","MenuCode":"2S_P","MenuUrl":"Manage/2Shou/p.aspx","IsMenu":"1"},{"MenuID":29,"MenuName":"二手车修改","MenuCode":"2S_Edit","MenuUrl":"Manage/2Shou/2CarEdit.aspx","IsMenu":"0"},{"MenuID":38,"MenuName":"我的拍卖","MenuCode":"2S_MyP","MenuUrl":"Manage/2Shou/mysell.aspx","IsMenu":"0"},{"MenuID":39,"MenuName":"竞拍终端页","MenuCode":"2S_PZD","MenuUrl":"Manage/2Shou/2CarView.aspx","IsMenu":"0"},{"MenuID":40,"MenuName":"我的竞拍","MenuCode":"2S_MYPP","MenuUrl":"Manage/2Shou/my_p.aspx","IsMenu":"1"}]},{"MenuID":34,"MenuName":"团购管理","MenuCode":"4s_Tuan","MenuCount":4,"MenuList":[{"MenuID":35,"MenuName":"添加团购","MenuCode":"4s_AddTuan","MenuUrl":"Manage/tuan/tuanadd.aspx","IsMenu":"1"},{"MenuID":37,"MenuName":"团购上传","MenuCode":"Tuan_Up","MenuUrl":"Manage/tuan/tuanpicupload.aspx","IsMenu":"0"},{"MenuID":42,"MenuName":"团购列表","MenuCode":"4s_TuanLis","MenuUrl":"Manage/tuan/tuanList.aspx","IsMenu":"1"},{"MenuID":43,"MenuName":"参加团购人员","MenuCode":"4s_tuanPeo","MenuUrl":"Manage/tuan/tuanListPeople.aspx","IsMenu":"0"}]},{"MenuID":41,"MenuName":"推广功能","MenuCode":"TG","MenuCount":1,"MenuList":[{"MenuID":15,"MenuName":"我要推广","MenuCode":"TG_Push","MenuUrl":"Manage/Extend/MyExtend.aspx","IsMenu":"1"}]},{"MenuID":10,"MenuName":"系统功能","MenuCode":"CF","MenuCount":6,"MenuList":[{"MenuID":11,"MenuName":"我的信息","MenuCode":"CF_ME","MenuUrl":"Manage/mydefault.aspx","IsMenu":"0"},{"MenuID":12,"MenuName":"系统模板","MenuCode":"CF_MD","MenuUrl":"Manage/Model/MyModel.aspx","IsMenu":"0"},{"MenuID":13,"MenuName":"员工管理","MenuCode":"CF_USERS","MenuUrl":"Manage/Users/MyUser.aspx","IsMenu":"1"},{"MenuID":14,"MenuName":"我要充值","MenuCode":"CF_Pay","MenuUrl":"Manage/Pay/Pay.aspx","IsMenu":"0"},{"MenuID":33,"MenuName":"4s信息管理","MenuCode":"CF_XXGL","MenuUrl":"Manage/edit4sInfo.aspx","IsMenu":"1"},{"MenuID":36,"MenuName":"系统菜单","MenuCode":"CF_Menu","MenuUrl":"Manage/Menu/Default.aspx","IsMenu":"0"}]}],"Rules":"Null"}]