The stored procedure parses the Table menu and outputs a JSON format string

The table structure is as follows:

SET ansi_nulls ongoset quoted_identifier ongoset ansi_padding ongocreate TABLE [dbo]. [T_4s_menu] ([f_menuid] [int] IDENTITY (max) not null,[f_menuname] [nvarchar] (null,[f_menuurl] [varchar] (+) Null,[f_parentid] [INT] Null,[f_menucode] [varchar] (TEN) 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]goset ansi_padding Offgo

The stored procedures are as follows:

SET ansi_nulls ongoset quoted_identifier ongo--=============================================--Author:<CSDN_ 5653325>--Create Date: <20130729>--description:<admin_menu to JSON type >--============================ =================alter PROCEDURE [dbo]. [Pr_4smenu2json] (@i_AdminID int, @i_GroupID int, @s_ReturnJson varchar () OUTPUT) Asbeginset NOCOUNT on;declare @JSON varchar (5000);--the JSON string to output DECLARE @root_MenuCount int;--root menu DECLARE @root_ MenuID int;--root menu iddeclare @root_MenuName nvarchar (50);--root Menu name declare @root_MenuCode varchar (10);--root menu encoding DECLARE @ Return_rules varchar (3000);--Returns the permissions of the query if (@i_AdminID =-1 and @i_GroupID =-1)--all beginselect @Return_Rules = ', "Rules": " Null "'; Endelsebeginif (@i_AdminID >-1)--Specify 4SIDBEGINSELECT @Return_Rules = ', {' +[f_rules]+ '} ' from [t_4s] WHERE [f_id][email  protected]_adminidendelse--Specify user Idbeginselect @Return_Rules = ', {' +[f_userpower]+ '} ' from [T_4s_users] WHERE [ID ][email protected]_groupidend END SELECT @root_MenuCount =counT (0) from T_4s_menu WHERE f_parentid=1 and f_menuid<>1 Select @JSON = ' [{' Count ': ' +convert (varchar), @root_ Menucount) + ', "list": [';D eclare @i int=1;--root menu loop counter, used to determine whether to read the last record 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 Ascopen Cur_rootfetch NEXT From Cur_root to @root_MenuID, @root_MenuName, @root_MenuCodeWHILE @ @FETCH_STATUS = 0BEGINDECLARE @MenuID int;-- submenu iddeclare @MenuName nvarchar (50);--submenu name declare @MenuCode varchar (10);--Sub-menu code declare @MenuUrl varchar (100);-- submenu Urldeclare @IsMenu bit;--submenu displays declare @MenuCount int;--submenu number declare @j int=1;--submenu loop counter to determine if the last record is a SELECT @ Menucount=count (0) from T_4s_menu WHERE [email protected]_menuiddeclare cur_menu CURSOR Forselect [F_MenuID],[F_ Menuname],[f_menucode],[f_menuurl],[f_ismenu] from T_4s_menu WHERE [email protected]_menuid ORDER by F_Order Ascopen Cur_menufetch NEXT from Cur_menu to @MenuID, @MenuName, @MenuCode, @MenUurl, @IsMenuIF @[email protected]_menucount--The last row of data beginselect @JSON + = ' {"MenuID": ' +convert (varchar (10), @ Root_menuid) + ', ' MenuName ': ' [email protected]_menuname+ ', ' menucode ': ' [email protected]_menucode+ '] , "Menucount": ' +convert (varchar), @MenuCount) + ', "menulist": [' while @ @FETCH_STATUS = 0BEGINIF @[email  Protected] Last record beginselect @JSON + = ' {"MenuID": ' +convert (varchar), @MenuID) + ', "MenuName": "' [email  Protected]+ ' "," Menucode ":" ' [email protected]+ ' "," Menuurl ":" ' [email protected]+ ' "," Ismenu ":" +convert (varchar (8), @IsMenu) + ' "' Select @JSON + = '}] ' END elsebeginselect @JSON + = ' {" MenuID ": ' +convert (varchar), @MenuID) + ', "MenuName": "' [email protected]+ '", "Menucode": "' [email protected]+ '", "Menuurl": "' [email   Protected]+ ' "," Ismenu ":" ' +convert (varchar (8), @IsMenu) + ' "' Select @JSON + = '}, ' select @j+=1; Endfetch NEXT from Cur_menu to @MenuID, @MenuName, @MenuCode, @MenuUrl, @IsMenuEND SELECT @JSON + = '} ' Endelsebeginselect @ json+= ' {"MenuID": ' +convert (varchAR (Ten), @root_MenuID) + ', "MenuName": "' [email protected]_menuname+ '", "Menucode": "' [email protected]_ Menucode+ ' "," menucount ": ' +convert (varchar), @MenuCount) + ', ' menulist ': [' while @ @FETCH_STATUS = 0BEGINIF @[email  protected] Last record beginselect @JSON + = ' {"MenuID": ' +convert (varchar), @MenuID) + ', "MenuName": "' [email  Protected]+ ' "," Menucode ":" ' [email protected]+ ' "," Menuurl ":" ' [email protected]+ ' "," Ismenu ":" +convert (varchar (8), @IsMenu) + ' "' Select @JSON + = '}] ' END elsebeginselect @JSON + = ' {" MenuID ": ' +convert (varchar), @MenuID) + ', "MenuName": "' [email protected]+ '", "Menucode": "' [email protected]+ '", "Menuurl": "' [email   Protected]+ ' "," Ismenu ":" ' +convert (varchar (8), @IsMenu) + ' "' Select @JSON + = '}, ' select @j+=1; Endfetch NEXT from Cur_menu into @MenuID, @MenuName, @MenuCode, @MenuUrl, @IsMenuENDSELECT @JSON + = '}, ' SELECT @i+=1; END Close Cur_menudeallocate Cur_menufetch NEXT from Cur_root to @root_MenuID, @root_MenuName, @root_MenuCodeEND CLOSE C Ur_rootdeallocate cur_rootselect @JSON + = '] ' [email protected]_rules+ '}] '; SELECT @s_ReturnJson = @JSONEND

The results are as follows:

[{"Count": 7, "list": [{"MenuID": 2, "MenuName": "Information Management", "Menucode": "NS", "Menucount": 2, "menulist": [{"MenuID": 3, " MenuName ":" Information release "," Menucode ":" Ns_edit "," Menuurl ":" Manage/news/newsedit.aspx "," Ismenu ":" 1 "},{" MenuID ": 17," MenuName ":" News list "," Menucode ":" Ns_nl "," Menuurl ":" Manage/news/newslist.aspx "," Ismenu ":" 1 "}]},{" MenuID ": 4," MenuName ":" New Car Management "," Menucode ":" 4C "," Menucount ": 2," menulist ": [{" MenuID ": 5," MenuName ":" Vehicle quote "," Menucode ":" 4c_pc "," Menuurl ":" Manage/cars/carprice.aspx "," Ismenu ":" 1 "},{" MenuID ": +," MenuName ":" Models sold "," Menucode ":" 4c_cl "," Menuurl " : "Manage/cars/sellcarlist.aspx", "Ismenu": "1"}]},{"MenuID": 6, "MenuName": "Message Consulting", "Menucode": "CM", "Menucount": 4, " Menulist ": [{" MenuID ": 7," MenuName ":" Test Drive "," Menucode ":" Cm_app "," Menuurl ":" manage/application/ Applicationlist.aspx "," Ismenu ":" 1 "},{" MenuID ": 8," MenuName ":" Reserve Price Advisory "," Menucode ":" Cm_zxdj "," Menuurl ":" manage/ Comment/zxdj.aspx "," Ismenu ":" 0 "},{" MenuID ": 9," MenuName ":" Customer Message "," Menucode ":" Cm_leave "," Menuurl ":" manage/ Leavemessage/leavemessage_list.aspx "," Ismenu ":" 1 "},{" MenuID ":"MenuName": "In-Station message", "Menucode": "Cm_mes", "Menuurl": "Manage/message/message_list.aspx", "Ismenu": "0"}]},{"MenuID ":" MenuName ":" Used Car Management "," Menucode ":" 2S "," Menucount ": 8," menulist ": [{" MenuID ":" MenuName ":" Used Cars list "," Menucode ":" 2s_list "," Menuurl ":" Manage/2shou/2carlist.aspx "," Ismenu ":" 1 "},{" MenuID ": +," MenuName ":" Used car Add "," Menucode ":" 2s_ ADD "," Menuurl ":" Manage/2shou/2caradd.aspx "," Ismenu ":" 1 "},{" MenuID ":" MenuName ":" Sold Vehicles "," Menucode ":" 2s_selled " , "Menuurl": "Manage/2shou/selled.aspx", "Ismenu": "0"},{"MenuID": "MenuName": "Bid platform", "Menucode": "2s_p", "Menuurl" : "Manage/2shou/p.aspx", "Ismenu": "1"},{"MenuID": "MenuName": "Used Cars modified", "Menucode": "2s_edit", "Menuurl": "manage/ 2shou/2caredit.aspx "," Ismenu ":" 0 "},{" MenuID ": $," MenuName ":" My Auctions "," Menucode ":" 2s_myp "," Menuurl ":" manage/2shou/ Mysell.aspx "," Ismenu ":" 0 "},{" MenuID ":," MenuName ":" Auction Terminal page "," Menucode ":" 2s_pzd "," Menuurl ":" manage/2shou/ 2carview.aspx "," Ismenu ":" 0 "},{" MenuID ": +," MenuName ":" My Auctions "," Menucode ":" 2s_mypp "," Menuurl ":" Manage/2shou/my_ P.aspx "," Ismenu ":" 1 "}]},{"MenuID": "MenuName": "Group Purchase Management", "Menucode": "4s_tuan", "Menucount": 4, "menulist": [{"MenuID": +, "MenuName": "Add Group Purchase" , "Menucode": "4s_addtuan", "Menuurl": "Manage/tuan/tuanadd.aspx", "Ismenu": "1"},{"MenuID": Notoginseng, "MenuName": "Buy upload", " Menucode ":" Tuan_up "," Menuurl ":" Manage/tuan/tuanpicupload.aspx "," Ismenu ":" 0 "},{" MenuID ": $," MenuName ":" Group Purchase List "," Menucode ":" 4s_tuanlis "," Menuurl ":" Manage/tuan/tuanlist.aspx "," Ismenu ":" 1 "},{" MenuID ": $," MenuName ":" Participate in group purchase personnel "," Menucode ":" 4s_tuanpeo "," Menuurl ":" Manage/tuan/tuanlistpeople.aspx "," Ismenu ":" 0 "}]},{" MenuID ": $," MenuName ":" Promotion function "," Menucode ":" TG "," Menucount ": 1," menulist ": [{" MenuID ":" MenuName ":" I want to promote "," Menucode ":" Tg_push "," Menuurl ": "Manage/extend/myextend.aspx", "Ismenu": "1"}]},{"MenuID": Ten, "MenuName": "System Function", "Menucode": "CF", "Menucount": 6, " Menulist ": [{" MenuID ": One," MenuName ":" My Message "," Menucode ":" Cf_me "," Menuurl ":" Manage/mydefault.aspx "," Ismenu ":" 0 "},{ "MenuID": "MenuName": "System Template", "Menucode": "Cf_md", "Menuurl": "Manage/model/mymodel.aspx", "Ismenu": "0"},{"MenuID" : "MenuName": "StaffManagement "," Menucode ":" Cf_users "," Menuurl ":" Manage/users/myuser.aspx "," Ismenu ":" 1 "},{" MenuID ": +," MenuName ":" I want to recharge "," Menucode ":" Cf_pay "," Menuurl ":" Manage/pay/pay.aspx "," Ismenu ":" 0 "},{" MenuID ":" MenuName ":" 4s information management "," Menucode ":" Cf_xxgl "," Menuurl ":" Manage/edit4sinfo.aspx "," Ismenu ":" 1 "},{" MenuID ": $," MenuName ":" System Menu "," Menucode ":" Cf_menu ", "Menuurl": "Manage/menu/default.aspx", "Ismenu": "0"}]}], "Rules": "Null"}]

Then the front desk can build the menu by JS parsing.

The stored procedure parses the Table menu and outputs a JSON format string