----------------------------------------------------------------------------------------- -- Procedure Name: spUpdateMenuStructure() -- Written By: Ben Santiago -- Created On: 06/07/2006 -- Description: -- This procedure will update the menu structure table (tblMenuStructure) based on -- the text-based menu tree supplied. -- -- Usage: -- spUpdateMenuStructure <ClientVersion[BigInt]>, <MenuStructure[String]> ----------------------------------------------------------------------------------------- CREATE PROCEDURE spUpdateMenuStructure @intClientVersion BIGINT, @strMenu VARCHAR(8000) AS BEGIN TRANSACTION ----------------------------------------- -- Initialize Variables ----------------------------------------- DECLARE @intCurrentVersion INT DECLARE @intCounter INT DECLARE @intTotalItems INT DECLARE @strItem VARCHAR(8000) ----------------------------------------- -- Default Values ----------------------------------------- SELECT @intCurrentVersion = [MenuVersion] FROM [tblTSDBSystem] WITH (SERIALIZABLE) IF @intCurrentVersion IS NULL SET @intCurrentVersion = 0 SET @intCounter = 1 ----------------------------------------- -- If Client Version Is Newer... ----------------------------------------- IF @intClientVersion > @intCurrentVersion BEGIN ----------------------------------------- -- Erase tblMenuStructure Data ----------------------------------------- DELETE tblMenuStructure ----------------------------------------- -- Count Number of Delimiters In String ----------------------------------------- SET @intTotalItems = 1 WHILE @intCounter <= LEN(@strMenu) BEGIN IF SUBSTRING(@strMenu, @intCounter, 1) = CHAR(240) SET @intTotalItems = @intTotalItems + 1 END ----------------------------------------- -- Parse Menu Structure String ----------------------------------------- WHILE @intCounter <= @intTotalItems BEGIN ----------------------------------------- -- Extract Current Array Item ----------------------------------------- SET @strItem = dbo.GetItemFromString(@strMenu, @intCounter, CHAR(240)) ----------------------------------------- -- Add Item To tblMenuStructure ----------------------------------------- INSERT tblMenuStructure VALUES (@strItem) ----------------------------------------- -- Increment Array Item Counter ----------------------------------------- SET @intCounter = @intCounter + 1 END ----------------------------------------- -- Create Temporary Table: tblRemovedItems ----------------------------------------- CREATE TABLE #tblRemovedItems ( [Group] CHAR(3), MenuText VARCHAR(1000), Visible BIT) ----------------------------------------- -- Select List of "Removed" Menu Items From tblMenuGroups ----------------------------------------- INSERT INTO #tblRemovedItems SELECT [Group], MenuText, Visible FROM (SELECT tblMenuGroups.[Group], tblMenuGroups.MenuText, tblMenuGroups.Visible, tblMenuStructure.MenuText AS MenuTextFilter FROM tblMenuGroups LEFT OUTER JOIN tblMenuStructure ON tblMenuGroups.MenuText = tblMenuStructure.MenuText ) UnMatched WHERE MenuTextFilter IS NULL ----------------------------------------- -- Delete "Removed" Items ----------------------------------------- IF @@ROWCOUNT > 0 BEGIN DELETE FROM tblMenuGroups WHERE MenuText IN (SELECT MenuText FROM #tblRemovedItems) END ----------------------------------------- -- Update MenuVersion Value ----------------------------------------- UPDATE [tblTSDBSystem] SET [MenuVersion] = @intClientVersion ----------------------------------------- -- Remove Temporary Table(s) ----------------------------------------- DROP TABLE #tblRemovedItems END ELSE ----------------------------------------- -- Rollback The Transaction ----------------------------------------- BEGIN ROLLBACK END ----------------------------------------- -- If The Transaction Is Still Open, Commit It ----------------------------------------- IF @@TRANCOUNT > 0 COMMIT