Need help with a stored procedure, re-ordering hierarchal data 1

[MikeL04]

I have a stored procedure that receives two comma separated lists, parsing them and inserting into a temp table. Selecting from the temp table shows what I am working with:

ParentID    PageID      MenuOrder   
----------- ----------- ----------- 
1           6           5
1           3           2
1           4           3
1           2           1
1           5           4
2           11          1
2           12          2
2           13          3
2           14          4
4           15          1
4           16          2
4           17          3
4           18          4
4           19          5

What I need to do is update my "main_Page" table with the MenuOrder resquenced as per the ParentID. For example the bove would look like the following, which I could then use to update based on a join of PageID: (* denotes a change from the preceding result set)

ParentID    PageID      MenuOrder   
----------- ----------- ----------- 
1           6           1 *
1           3           2
1           4           3
1           2           4 *
1           5           5 *
2           11          1
2           12          2
2           13          3
2           14          4
4           15          1
4           16          2
4           17          3
4           18          4
4           19          5

Any ideas on how to accomplish this? I am stumped. I included what I have so far below.

Thanks,
Mike


My Proceedure call is sown below and the procedure so far follows:

Page_UpdateNavMenuOrder '1,6,1,1,4,4,4,17,17,17,4,4,1,2,2,2,2,1,5', '6,24,3,4,15,16,17,20,21,22,18,19,2,11,12,13,14,5,23'

ALTER PROCEDURE dbo.Page_UpdateNavMenuOrder
	@ParentIDsList varchar(1000)
	,@PageIDsList  varchar(1000)
AS

SET NOCOUNT ON

---- Phase 1: parse the character delimeted strings

DECLARE
	@ParentID   int
	,@PageID    int
	,@EndMarker int
	,@Counter   int


CREATE TABLE #tmpMenuItems (
	ParentID     int
	,PageID      int
	,InsertOrder int
)

SET @Counter = 1

SET @EndMarker = CHARINDEX(',', @ParentIDsList)

WHILE (@EndMarker > 0)
  BEGIN
	SET @ParentID = SUBSTRING(@ParentIDsList, 1, @EndMarker - 1)
	SET @ParentIDsList = SUBSTRING(@ParentIDsList, @EndMarker + 1, LEN(@ParentIDsList))

	SET @EndMarker = CHARINDEX(',', @PageIDsList)

	IF (@EndMarker > 0)
 	  BEGIN
		SET @PageID = SUBSTRING(@PageIDsList, 1, @EndMarker - 1)
		SET @PageIDsList = SUBSTRING(@PageIDsList, @EndMarker + 1, LEN(@PageIDsList))
	  END
	ELSE
	  BEGIN
		SELECT 'FAIL' as [Status],
			'Missing PageID for ParentID ' + STR(@ParentID) as [Reason]
		RETURN
	  END

	INSERT #tmpMenuItems (
		ParentID
		,PageID
		,InsertOrder
	)
	VALUES (
		@ParentID
		,@PageID
		,@Counter
	)

	SET @EndMarker = CHARINDEX(',', @ParentIDsList)
	SET @Counter = @Counter + 1
END

IF CHARINDEX(',', @PageIDsList ) > 0
  BEGIN
	SELECT 'FAIL' as [Status],
		'Unbalanced lists - PageID = ' + STR(@PageIDsList) + ' - ParentID = ' + STR(@ParentID) as [Reason]
	RETURN
  END

INSERT #tmpMenuItems (
	ParentID
	,PageID
	,InsertOrder
)
VALUES (
	@ParentIDsList
	,@PageIDsList
	,@Counter
)

SELECT
	t.ParentID
	,t.PageID
	,MenuOrder
FROM
	#tmpMenuItems  t
	JOIN main_Page p ON t.PageID = p.PageID
ORDER BY
	t.ParentID
	,t.InsertOrder

-- resequence and update here...

DROP TABLE #tmpMenuItems

 

[MikeL04]

Well I got it to do what I wanted and it is working great, however the only way I could think of doing it was using a cursor which I would like to avoid if at all possible.

Below is the completed proc (with a cursor.. yuk) and its output. Any ideas how to accomplish this without using a cursor? I REALLY don't want to use a cursor!

Thanks
Mike

OUTPUT (for testing purposes):

Before resequencing:
ParentID    PageID      MenuOrder   
----------- ----------- ----------- 
1           2           3
1           3           1
1           4           2
1           5           4
1           6           5
2           11          4
2           12          1
2           13          2
2           14          3
4           15          1
4           16          3
4           17          4
4           18          2
4           19          5
5           23          1
6           24          1
17          20          3
17          21          1
17          22          2

After resequencing:
ParentID    PageID      MenuOrder   
----------- ----------- ----------- 
1           2           1
1           3           2
1           4           3
1           5           4
1           6           5
2           11          1
2           12          2
2           13          3
2           14          4
4           15          1
4           16          2
4           17          3
4           18          4
4           19          5
5           23          1
6           24          1
17          20          1
17          21          2
17          22          3

status  reason                                              
------- --------------------------------------------------- 
SUCCESS Update completed successfully, transaction commited

PROCEDURE:

ALTER                     PROCEDURE dbo.Page_UpdateNavMenuOrder
	@ParentIDsList varchar(1000)
	,@PageIDsList  varchar(1000)
AS

SET NOCOUNT ON

---- Phase 1: parse the character delimeted strings

DECLARE
	@ParentID      int
	,@PageID       int
	,@EndMarker    int
	,@Counter      int

CREATE TABLE #tmpMenuItems (
	ParentID     int
	,PageID      int
	,InsertOrder int
)

SET @Counter = 1

SET @EndMarker = CHARINDEX(',', @ParentIDsList)

WHILE (@EndMarker > 0)
  BEGIN
	SET @ParentID = SUBSTRING(@ParentIDsList, 1, @EndMarker - 1)
	SET @ParentIDsList = SUBSTRING(@ParentIDsList, @EndMarker + 1, LEN(@ParentIDsList))

	SET @EndMarker = CHARINDEX(',', @PageIDsList)

	IF (@EndMarker > 0)
 	  BEGIN
		SET @PageID = SUBSTRING(@PageIDsList, 1, @EndMarker - 1)
		SET @PageIDsList = SUBSTRING(@PageIDsList, @EndMarker + 1, LEN(@PageIDsList))
	  END
	ELSE
	  BEGIN
		SELECT 'FAIL' as [Status],
			'Missing PageID for ParentID ' + STR(@ParentID) as [Reason]
		RETURN
	  END

	INSERT #tmpMenuItems (
		ParentID
		,PageID
		,InsertOrder
	)
	VALUES (
		@ParentID
		,@PageID
		,@Counter
	)

	SET @EndMarker = CHARINDEX(',', @ParentIDsList)
	SET @Counter = @Counter + 1
END

IF CHARINDEX(',', @PageIDsList ) > 0
  BEGIN
	SELECT 'FAIL' as [Status],
		'Unbalanced lists - PageID = ' + STR(@PageIDsList) + ' - ParentID = ' + STR(@ParentID) as [Reason]
	RETURN
  END

INSERT #tmpMenuItems (
	ParentID
	,PageID
	,InsertOrder
)
VALUES (
	@ParentIDsList
	,@PageIDsList
	,@Counter
)

---- Phase 2: resequence the MenuOrder based on the groups of ParentIDs

CREATE TABLE #tmpMenuItemsOrdered (
	ParentID   int
	,PageID    int
	,MenuOrder int
)

DECLARE @PrevParentID int

DECLARE cursMenuItems CURSOR FOR
	SELECT
		t.ParentID
		,t.PageID
		--,MenuOrder
	FROM
		#tmpMenuItems  t
		JOIN main_Page p ON t.PageID = p.PageID
	ORDER BY
		t.ParentID
		,t.InsertOrder

-- get the root ParentID
SELECT
	@PrevParentID = MIN(ParentID)
FROM
	main_Page
WHERE
	ParentID <> 0

SET @Counter = 1

OPEN cursMenuItems
	-- Get first record
	FETCH NEXT FROM cursMenuItems
	INTO @ParentID, @PageID
	
	WHILE @@FETCH_STATUS = 0 -- While there are more records
	  BEGIN		
		IF @ParentID <> @PrevParentID
		  BEGIN
			SET @Counter = 1
		  END

		INSERT #tmpMenuItemsOrdered (
			ParentID
			,PageID
			,MenuOrder
		)
		VALUES (
			@ParentID
			,@PageID
			,@Counter
		)		

		SET @PrevParentID = @ParentID
		SET @Counter = @Counter + 1

		-- Get next row
	    	FETCH NEXT FROM cursMenuItems
	    	INTO @ParentID, @PageID
	  END	
CLOSE cursMenuItems
DEALLOCATE cursMenuItems


PRINT 'Before resequencing:'

SELECT
	t.ParentID
	,t.PageID
	,MenuOrder
FROM
	#tmpMenuItems  t
	JOIN main_Page p ON t.PageID = p.PageID
ORDER BY
	t.ParentID
	,t.InsertOrder


DROP TABLE #tmpMenuItems


PRINT 'After resequencing:'

SELECT
	*
FROM
	#tmpMenuItemsOrdered

---- Phase 3: store the new order

BEGIN TRANSACTION
	UPDATE 
		main_Page
	SET	
		MenuOrder = b.MenuOrder
	FROM 	
		main_Page                 a
		JOIN #tmpMenuItemsOrdered b ON a.PageID = b.PageID
	
	IF @@ERROR != 0
	  BEGIN
		ROLLBACK TRANSACTION
		SELECT 'FAIL' as [Status]
		       ,'Error updating main_Page' as [Reason]
		RETURN
	  END
COMMIT TRANSACTION

DROP TABLE #tmpMenuItemsOrdered

SELECT 
	'SUCCESS' AS [status]
	,'Update completed successfully, transaction commited' AS [reason]

 

[zhavic]

Try something like this:

CREATE  FUNCTION dbo.fn_string_to_table
	( @cList varchar(1000) )
	RETURNS @Table_list TABLE ( Item integer, ID_Key integer )
AS
BEGIN

	DECLARE @i integer,
			@nComma_marker integer

	DECLARE @cItem varchar(100)

	SET @i = 1
	SET @cList = RTRIM( @cList )
	WHILE @cList != ''
	BEGIN

		SET @nComma_marker = CHARINDEX( ',', @cList )
		IF @nComma_marker > 0
		BEGIN
			SELECT @cItem = LEFT( @cList, @nComma_marker - 1 )
			SET @cList = RIGHT( @cList, LEN( @cList ) - @nComma_marker )
		END
		ELSE
		BEGIN
			/* last item */
			SELECT @cItem = RTRIM( @cList )
			SET @cList = ''
		END

		INSERT INTO @Table_list
			VALUES( @cItem, @i )

		SET @i = @i + 1
	END
	
	RETURN
END

GO

CREATE PROCEDURE dbo.Page_UpdateNavMenuOrder
    @ParentIDsList varchar(1000)
    ,@PageIDsList  varchar(1000)
AS

SET NOCOUNT ON


/* create tables for lists */
CREATE TABLE #PageIDsList ( PageID integer, ID_key integer )
CREATE TABLE #ParentIDsList ( ParentID integer, ID_key integer )

/* create tables for temporary results */
CREATE TABLE #tmp1 ( ParentID integer, PageID integer, ID_key int IDENTITY ( 1, 1 ) )
CREATE TABLE #tmp2 ( MenuOrder integer, ID_key int IDENTITY ( 1, 1 ) )

/* fill tables from lists */
INSERT INTO #PageIDsList
	SELECT * FROM dbo.fn_string_to_table( @PageIDsList )
INSERT INTO #ParentIDsList
	SELECT * FROM dbo.fn_string_to_table( @ParentIDsList )

/* check passed values */
IF ( SELECT COUNT( ID_key ) FROM #ParentIDsList ) != ( SELECT COUNT( ID_key ) FROM #PageIDsList )
BEGIN
	SELECT 'FAIL' as [Status],
        'Unbalanced lists - PageID = ' + @PageIDsList + ' - ParentID = ' + @ParentIDsList as [Reason]
    RETURN
END

/* create result with ParentID and PageID ordered by ParentID and sequence from passed list of ParentIDs, 
   but only that have corensponding rows in main_page */
INSERT INTO #tmp1 ( ParentID, PageID )
 SELECT #ParentIDsList.ParentID,
		#PageIDsList.PageID
	FROM #ParentIDsList
	INNER JOIN #PageIDsList ON #ParentIDsList.ID_Key = #PageIDsList.ID_Key
	INNER JOIN main_page ON main_Page.PageID = #PageIDsList.PageID
	ORDER BY #ParentIDsList.ParentID, #ParentIDsList.ID_Key

/* MenuOrder values for each ParentID ordered by MenuOrder */
INSERT INTO #tmp2 ( MenuOrder )
	SELECT MenuOrder
		FROM main_page
		INNER JOIN #tmp1 ON main_Page.PageID = #tmp1.PageID
		ORDER BY ParentID, MenuOrder

/* desired result */
SELECT #tmp1.ParentID,
	   #tmp1.PageID,
	   #tmp2.MenuOrder
	FROM #tmp1
	INNER JOIN #tmp2 ON #tmp2.ID_Key = #tmp1.ID_Key
	ORDER BY #tmp1.ParentID, #tmp2.ID_Key

GO

--------------------------------------------------
Than try
EXECUTE Page_UpdateNavMenuOrder '1,6,1,1,4,4,4,17,17,17,4,4,1,2,2,2,2,1,5', '6,24,3,4,15,16,17,20,21,22,18,19,2,11,12,13,14,5,23'



Zhavic

---------------------------------------------------------------
In the 1960s you needed the power of two Comodore64s to get a rocket to the moon. Now you need a machine which is a vast number of times more powerful just to run the most popular GUI.

 

[MikeL04]

Wow, I never thought of doing it that way. (obviously since I used a cursor!)

Thanks for taking the time to go through the whole thing and provide an alternate method! I told the PM that it was working and that was good enough for him, however I am definately going to be trying your method and sneak it in there on my own time. I'll keep you posted on how it goes. Anytime you can avoid the use of a cursor is a good thing!

Thanks again,

Mike

 

[zhavic]

Thanks for star, glad I could help.

I never thought of doing it that way.
Don't worry, it's based only on experiences. ( and I have little of them - I am working with SQL less than one year )

As someone there sad, experts are only people that read more than others.

Complexity of that algorhytms is based on data you have.
For example, that algorhytm I show you can be little simple, if you can guarantee, that 'MenuOrder' will contain values with no gaps between numbers and always start with 1 ( e.g. it can't be 1,2,5,6,9,... but always must be 1,2,3,4,5,6,7, ... )

Zhavic

---------------------------------------------------------------
In the 1960s you needed the power of two Comodore64s to get a rocket to the moon. Now you need a machine which is a vast number of times more powerful just to run the most popular GUI.