问题描述
我正在尝试编写一个递归过程,该过程将删除节点及其所有子节点(如果它们在表中).我尝试执行以下操作
I am trying to write a recursive procedure that would delete the node and all it's children if they are such in the table. I tried doing the following
CREATE PROCEDURE DeleteFile @FileID INTEGER, @UserID VARCHAR(MAX) AS DELETE FROM [FileTree] WHERE [ID] = @FileID AND [UserID]=@UserID; IF EXISTS(SELECT * FROM [FileTree] WHERE [ParentID] = @FileID AND [UserID]=@UserID) BEGIN DECLARE FileCursor CURSOR LOCAL FOR SELECT [ID],[UserID] FROM [FileTree] WHERE [ParentID] = @FileID AND [UserID]=@UserID; OPEN FileCursor FETCH NEXT FROM FileCursor INTO @FileID , @UserID WHILE @@FETCH_STATUS =0 BEGIN EXEC DeleteFile @FileID,@UserID; FETCH NEXT FROM FileCursor INTO @FileID , @UserID ; END END ELSE return
但由于某种原因,这不起作用.它删除了节点,但孩子们仍然存在.表,,设计".
But for some reason this is not working. It deletes the node but the kids remain. Table ,,design" .
CREATE TABLE [FileTree] ( [ID] INT IDENTITY NOT NULL, [Name] VARCHAR (MAX) NOT NULL, [ParentID] INT NULL, [UserID] VARCHAR (MAX) NOT NULL );
你能指出我犯的错误并建议一个工作程序吗?UPD:我将游标设为 LOCAL 并且在进入 while 循环之前我正在获取一次,但它仍然没有删除所有的孩子.
Can you please indicate the errors I made and suggest a working procedure ? UPD: I made the cursor LOCAL and I am fetching one time before going into the while loop, it still does not delete all the children.
推荐答案
我认为你有语法问题.我是这样修复的
I think you have syntax problem.i fixed it like this
CREATE PROCEDURE DeleteFile @FileID INTEGER, @UserID VARCHAR(MAX) AS BEGAIN DELETE FROM [FileTree] WHERE [ID] = @FileID AND [UserID]=@UserID; IF EXISTS(SELECT * FROM [FileTree] WHERE [ParentID] = @FileID AND [UserID]=@UserID) BEGIN DECLARE FileCursor CURSOR LOCAL FOR SELECT [ID],[UserID] FROM [FileTree] WHERE [ParentID] = @FileID AND [UserID]=@UserID; OPEN FileCursor FETCH NEXT FROM FileCursor INTO @FileID , @UserID WHILE @@FETCH_STATUS =0 BEGIN EXEC DeleteFile @FileID,@UserID; FETCH NEXT FROM FileCursor INTO @FileID , @UserID ; END END END