SQL Server Stored Procedure (Menu System) - Microsoft SQL Server 2005

Question

Suppose - I have the following Table Structure

elementid, parentid,  elementtitle,          sortorder
160  0             Brand New Tutorial      1
161  160           Brand New Tutorial New Step   1
168  5           Tutorial Topic 1.1      1
171  168           Tutorial Topic 1.1.1         1
172  171           Tutorial Topic 1.1.1.1     1

I need to be able to setup a Stored Procedure that will allow me to Update the Elementid's, Parentid's relationship.

Here is my Normal SQL For Generating the tree:

 WITH menu AS 
 (
 SELECT parentid, elementid, elementtitle, sortorder FROM dbo.ta_tutorial_elements WHERE (elementid = @eid)
    UNION ALL
    SELECT e.parentid, e.elementid, e.elementtitle, e.sortorderFROM dbo.ta_tutorial_elements AS e INNER JOIN menu AS m ON e.parentid = m.elementid
 )
 SELECT * INTO [#tmpA] FROM menu

I believe it could be possible to use temp tables to copy the table over and then somehow use the identity of the insert into my standard table to start with the elementid and the root parent...however, after that I am pretty much lost on how to recursively udpate all parentid's, elementid's with their relationships...(is it possible in SQL Server?).

I am seeing something like the following:

CREATE PROCEDURE [dbo].[sp_ta_copy_tutorial_by_id] @eid bigint AS SET nocount on BEGIN DECLARE @recid bigint

SET @recid      = (SELECT IDENT_CURRENT('ta_tutorial_elements'));

WITH menu AS 
(
SELECT parentid, elementid, elementtitle, sortorder, userid, createddate FROM dbo.ta_tutorial_elements WHERE (elementid = @eid)
   UNION ALL
   SELECT e.parentid, e.elementid, e.elementtitle, e.sortorder, e.userid, e.createddate FROM dbo.ta_tutorial_elements AS e INNER JOIN menu AS m ON e.parentid = m.elementid
)
SELECT * INTO [#tmpA] FROM menu

ALTER TABLE [#tmpA]
DROP COLUMN elementid

SELECT * INTO [#tmpB] FROM [#tmpA];

UPDATE b SET b.parentid =
    CASE
        WHEN b.parentid <> 0 
        THEN @recid
        ELSE 0
    END
FROM [#tmpB] as b

INSERT INTO [ta_tutorial_elements] SELECT * FROM [#tmpB]
DROP TABLE [#tmpA]
DROP TABLE [#tmpB]

END

Answer 1

Check out CTEs (common table expressions). You can use a CTE within the context of a stored procedure to yield your menu data after some recursion. Actually it looks like you already have a CTE; the link describes recursion within that context.

Answer 2

CREATE PROCEDURE [dbo].[sp_ta_copy_tutorial_by_id]
    @eid bigint
AS
SET nocount on
BEGIN
/***************************************
*     CREATE A
***************************************/
SELECT            *
INTO        #tmpA
FROM        ta_tutorial_elements
WHERE       elementid IN (SELECT elementid from fn_ta_tutorial_tree_by_element (@EID))

/***************************************
*     DUPLICATE RECORDS
***************************************/
DECLARE           @CNT INT
SET               @CNT = (SELECT max(elementid) FROM ta_tutorial_elements)

INSERT            
INTO        ta_tutorial_elements (elementtitle, parentid)
            SELECT elementtitle, parentid FROM #tmpA

/***************************************
*     CREATE B 
***************************************/
SELECT            * 
INTO        #tmpB
FROM        ta_tutorial_elements
WHERE       elementid > @CNT

SELECT            bpid.elementid as originalelementid, 
                  brow.elementid as newparentid
INTO        #tmpC
FROM        #tmpB bpid LEFT OUTER JOIN
                  #tmpA aeid ON bpid.parentid = aeid.elementid LEFT OUTER JOIN
                  (
                        SELECT            elementid, ROW_NUMBER () OVER (ORDER BY elementid ASC) as rownum
                        FROM        #tmpA
                  ) arow ON arow.elementid = aeid.elementid LEFT OUTER JOIN
                  (
                        SELECT            elementid, ROW_NUMBER () OVER (ORDER BY elementid ASC) as rownum
                        FROM        #tmpB
                  ) brow ON brow.rownum = arow.rownum LEFT OUTER JOIN
                  #tmpB beid ON beid.elementid = brow.elementid 

UPDATE            #tmpC 
SET               newparentid = 0
WHERE       newparentid IS NULL

UPDATE            t2
SET               parentid = t1.newparentid
FROM        #tmpC t1 LEFT OUTER JOIN
                  ta_tutorial_elements t2 ON t1.originalelementid = t2.elementid

/***************************************
*     TEMP DISPLAY
***************************************/
SELECT * FROM ta_tutorial_elements WHERE elementid > @CNT

DROP TABLE  #tmpA
DROP TABLE  #tmpB
DROP TABLE  #tmpC

END