Developing Matt

Don’t you just love an opportunity to use recursive logic?  I sure do.  So here’s a little sp I wrote to recursively set up a menu.  I suppose you could use it in other ways as well…but the bottom line is that I call this from a parent sp and it recursively builds a menu system (based upon an existing one).  Recursive calls are great for building heirarchial sets such as this.

Also, as part of this I use a table variable. 

– =============================================
– Author:        matt clingan
– Create date: 4/1/2008
– Description:   called by insert menu to insert child menu items
– =============================================

CREAT PROCEDURE [dbo].[InsertMenuChild]

      @OldMenuId smallint,

      @NewCompany varchar(10),

      @NewParentId smallint

AS

BEGIN

SET NOCOUNT ON;

 

–set up datatypes for table variable

declare @menuid smallint, @parentid smallint, @MenuOrder float, @MenuType varchar(20), @Active bit,

@Company varchar(25),@Text varchar(17), @Description varchar(30), @URL varchar(80), @ImgURL varchar(50),

@NormCssClass varchar(30), @HoverCssClass varchar(30),@MenuCategory varchar(20), @Page varchar(40),@Access  varchar(100)

 

–set up table variable

declare @tbl table(id int identity(1,1), menuid smallint, parentid smallint, MenuOrder float, MenuType varchar(20), Active bit,

Company varchar(25),Text varchar(17), Description varchar(30), URL varchar(80), ImgURL varchar(50),

NormCssClass varchar(30), HoverCssClass varchar(30),MenuCategory varchar(20), Page varchar(40),Access  varchar(100))

 

declare @rowcount int

declare @icount int

declare @newMenuID smallint

 

–INSERT into table variable the information based on existing menu item

insert into @tbl (menuid, parentid, MenuOrder, MenuType, Active, Company,

Text, Description, URL, ImgURL, NormCssClass, HoverCssClass,MenuCategory, Page,Access )

 select menuid, parentid, MenuOrder, MenuType, Active, Company,

Text, Description, URL, ImgURL, NormCssClass, HoverCssClass,MenuCategory, Page,Access 

from menu where parentid = @OldMenuID order by menuid

 

 

–set up and begin loop  

set @rowcount = @@rowcount

set @icount = 1

 

while @icount <= @rowcount

begin

 

select @menuid = menuid, @parentid = parentid, @menuOrder = menuOrder, @menutype = menutype, @Active = active, @Company = company, @text = text, @description = description, @url = url , @imgurl = imgurl, @normcssClass = normCssClass, @hoverCssClass = hoverCssClass, @MenuCategory  = @MenuCategory, @Page = page, @access = access from @tbl where id = @icount

 

insert into menu (parentid, MenuOrder, MenuType, Active, Company,Text, Description, URL, ImgURL, NormCssClass, HoverCssClass,MenuCategory, Page,Access)values (@newParentID, @MenuOrder, @MenuType, @Active, @NewCompany, @Text, @Description, @URL, @ImgURL, @NormCssClass, @HoverCssClass,@MenuCategory, @Page,@Access)

 

set @newMenuid = @@identity

–recursively call same procedure to build menu system

execute InsertMenuChild @menuid, @newCompany, @newMenuid

 

set @icount = @icount +1

 

end

END