I am building up a product catalog. I use one table Category(id, name, parentId, isExpanded) to store all the categories in a tree structure. "id" is an identity column that has its value auto-incremented. "parentId" is a foreign key that references the "id" column of the same table.A category copy function is required, which should be able to copy a specific tree branch and put it under another tree node. After the branch is copied, all the nodes involved should be returned to another component with their old "id"s and new "id"s. I need to use a stored procedure to implement this copy function. But I was disappointingly stuck with the "insert" statement not working within the second "while" loop (as shown in the following code). No error was returned, there is just nothing inserted in the table at all.The first parameter of the procedure is the id of the top-most node of the branch to copy and the second parameter is the id of the destination node to which the copied branch will be appended.Please help me check out the code. And if possibly, please run this procedure on your server and see whether it will work. Thank a lot.ALTER PROCEDURE Category_Copy @id int, @parentId intAS DECLARE @children TABLE (rowId BIGINT IDENTITY(1,1), [id] int, [parentId] int default null, [level] int, [newId] int default null, [name] varchar(100) default null ) DECLARE @level intBEGIN INSERT INTO @children ([id], [parentId], [level], [newId], [name]) SELECT @id, [parentId], -1, null, [name] from Category WHERE id = @id SET @level = 0 INSERT INTO @children ([id], [parentId], [level], [newId], [name]) SELECT [id], [parentId], @level, null, [name] from Category WHERE parentID = @id WHILE @@ROWCOUNT > 0 BEGIN SET @level = @level + 1 INSERT INTO @children ([id], [parentId], [level], [newId], [name]) SELECT c.[id], c.[parentId], @level, null, c.[name] FROM Category c JOIN @children i ON c.[parentId] = i.[id] AND i.[level] = @level -1 END -- Prepare additional variables for looping through the table variable DECLARE @varMaxCount BIGINT DECLARE @varCurrentCount BIGINT SELECT @varMaxCount = max([rowId]) FROM @children -- End Prepare -- Loop through the table variable DECLARE @intLoops INT SET @intLoops = 0 -- Debuging line WHILE @varCurrentCount <= @varMaxCount BEGIN DECLARE @curId INT DECLARE @curParentId INT DECLARE @curLevel INT DECLARE @curName VARCHAR(100) -- Retrieve column values from the current row of the table variable SELECT @curId = [id], @curParentId = [parentId], @curLevel = [level], @curName = [name] FROM @children WHERE [rowId] = @varCurrentCount -- End Retrieve DECLARE @curNewId INT DECLARE @curNewParentId INT IF @curLevel < 0 SELECT @curNewParentId = @parentId ELSE SELECT @curNewParentId = [newId] FROM @children WHERE [id] = @curParentId --EXEC Category_Add @curName, @curNewParentId, 0, @curNewId OUTPUT insert into category ([name], [parentId]) values (@curName, @curNewParentId) set @curNewId = @@Identity update @children set [newId] = @curNewId where [id] = @curId SET @varCurrentCount = @varCurrentCount+1 SET @intLoops = @intLoops + 1 -- Debuging line END -- End Loop -- Reset the loop counter variable SET @varCurrentCount = 1 -- End Reset SELECT [rowId], [id], [parentId], [level], [newId], [name] FROM @children ORDER BY [rowId] ASC --return @damn --@varMaxCount --@intLoopsEND
May the force be with you.
Stored Procedures - Weird bug: insert not working
Posted - 2008-11-14 : 07:50:24
