Добрый день!

Удаление узла вместе с дочерними узлами можно выполнять и без рекурсии, и без курсоров.
Пример итеративного подхода, на реальной БД (AnAccountAndItsAncestors - служебная таблица для раскрутки иерархий):

CREATE VIEW
akViewAccountLevel
AS
SELECT
AccountId,
COUNT(AncestorId) AS AccountLevel
FROM
AnAccountAndItsAncestors
GROUP BY
AccountId
GO

SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE akDropCustomAccount @AccountId AnAccountId, @CheckRights BIT = 1
AS
BEGIN
/* initialize execution state */
DECLARE @Result INT
SET NOCOUNT ON
SET @Result = 0

BEGIN TRANSACTION
IF (@CheckRights = 1) AND (dbo.akHasConstraint('Accounting:Plan:Change') = 0)
BEGIN
SET @Result = -101
GOTO fin
END

/* drop children and self - one level per iteration */
WHILE EXISTS(SELECT TOP 1 * FROM AnAccountAndItsAncestors WHERE (AncestorId = @AccountId))
BEGIN
/* one step is dropping of children which nesting level is maximal */
DELETE FROM ACustomAccount WHERE
"Id" IN (
SELECT
A1.AccountId
FROM
AnAccountAndItsAncestors A1
INNER JOIN akViewAccountLevel AL1 ON (AL1.AccountId = A1.AccountId)
WHERE
(A1.AncestorId = @AccountId)
AND (
AL1.AccountLevel = (
SELECT MAX(AL2.AccountLevel) FROM akViewAccountLevel AL2 INNER JOIN AnAccountAndItsAncestors A2 ON (AL2.AccountId = A2.AccountId) WHERE (A2.AncestorId = @AccountId)
)
)
)
SET @Result = @@ERROR
IF @Result <> 0 GOTO fin
END

/* finalize transaction */
fin:
IF @@TRANCOUNT > 0
BEGIN
IF @Result <> 0 ROLLBACK TRANSACTION ELSE COMMIT TRANSACTION
END
RETURN @Result
END
GO


А вот реальная схема БД:
if exists (select * from sysobjects where id = object_id('"ACustomAccount"') and sysstat & 0xf = 3)
drop table "ACustomAccount"
GO

CREATE TABLE "ACustomAccount" (
"Id" "AnAccountId" IDENTITY (1, 1) NOT NULL ,
"IsRoot" "AnUnorderedBoolean" NOT NULL CONSTRAINT "Set_to_false14" DEFAULT (0),
"IsSub" "AnUnorderedBoolean" NOT NULL CONSTRAINT "Set_to_false15" DEFAULT (0),
"IsFolder" "AnUnorderedBoolean" NOT NULL CONSTRAINT "Set_to_false16" DEFAULT (0),
"IsLeaf" "AnUnorderedBoolean" NOT NULL CONSTRAINT "Set_to_false17" DEFAULT (0),
"IsInClientBalance" "AnUnorderedBoolean" NOT NULL CONSTRAINT "Set_to_false18" DEFAULT (0),
CONSTRAINT "XPKACustomAccount" PRIMARY KEY CLUSTERED
(
"Id"
) WITH FILLFACTOR = 90
)
GO


if exists (select * from sysobjects where id = object_id('"AFolderAccount"') and sysstat & 0xf = 3)
drop table "AFolderAccount"
GO

CREATE TABLE "AFolderAccount" (
"Id" "int" NOT NULL ,
CONSTRAINT "XPKAFolderAccount" PRIMARY KEY CLUSTERED
(
"Id"
) WITH FILLFACTOR = 90,
CONSTRAINT "aca_is_afa" FOREIGN KEY
(
"Id"
) REFERENCES "ACustomAccount" (
"Id"
) ON DELETE CASCADE
)
GO


if exists (select * from sysobjects where id = object_id('"ASubAccount"') and sysstat & 0xf = 3)
drop table "ASubAccount"
GO

CREATE TABLE "ASubAccount" (
"Id" "int" NOT NULL ,
"FolderAccountId" "int" NOT NULL ,
"Code" "AnAccountCode" NOT NULL ,
"Path" "varchar" (8000) COLLATE SQL_Latin1_General_CP1251_CI_AS NOT NULL ,
CONSTRAINT "XPKASubAccount" PRIMARY KEY NONCLUSTERED
(
"Id"
) WITH FILLFACTOR = 90,
CONSTRAINT "XAKASubAccountPath" UNIQUE NONCLUSTERED
(
"Path"
) WITH FILLFACTOR = 90,
CONSTRAINT "XAKSubAccountCode" UNIQUE NONCLUSTERED
(
"FolderAccountId",
"Code"
) WITH FILLFACTOR = 90,
CONSTRAINT "aca_is_asa" FOREIGN KEY
(
"Id"
) REFERENCES "ACustomAccount" (
"Id"
) ON DELETE CASCADE ,
CONSTRAINT "contain_subaccounts" FOREIGN KEY
(
"FolderAccountId"
) REFERENCES "AFolderAccount" (
"Id"
),
CONSTRAINT "IsValidAccountCode74" CHECK (not([Code] like '%\%'))
)
GO


if exists (select * from sysobjects where id = object_id('"AnAccountAndItsAncestors"') and sysstat & 0xf = 3)
drop table "AnAccountAndItsAncestors"
GO

CREATE TABLE "AnAccountAndItsAncestors" (
"AncestorId" "int" NOT NULL ,
"AccountId" "int" NOT NULL ,
CONSTRAINT "XPKAnAccountAndItsAncestors" PRIMARY KEY CLUSTERED
(
"AncestorId",
"AccountId"
) WITH FILLFACTOR = 90,
CONSTRAINT "account_under_ancestors" FOREIGN KEY
(
"AccountId"
) REFERENCES "ACustomAccount" (
"Id"
) ON DELETE CASCADE ,
CONSTRAINT "is_ancestor_of" FOREIGN KEY
(
"AncestorId"
) REFERENCES "ACustomAccount" (
"Id"
)
)
GO


SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO

if exists (select * from sysobjects where id = object_id('"dbo"."akCreateACustomAccount"') and sysstat & 0xf = 4)
drop procedure "dbo"."akCreateACustomAccount"
GO

CREATE PROCEDURE akCreateACustomAccount @Id AnAccountId OUTPUT, @CheckRights BIT = 1
AS
BEGIN
/* initialize execution state */
DECLARE @Result INT
SET NOCOUNT ON
SET @Result = 0
SET @Id = -1

BEGIN TRANSACTION
IF (@CheckRights = 1) AND (dbo.akHasConstraint('Accounting:Plan:Change') = 0)
BEGIN
SET @Result = -101
GOTO fin
END

INSERT
INTO ACustomAccount
DEFAULT VALUES
SET @Result = @@ERROR

IF @Result = 0
BEGIN
SET @Id = @@IDENTITY
INSERT
INTO AnAccountAndItsAncestors(AccountId, AncestorId)
VALUES(@Id, @Id)
SET @Result = @@ERROR
END

/* finalize transaction */
fin:
IF @@TRANCOUNT > 0
BEGIN
IF @Result <> 0 ROLLBACK TRANSACTION ELSE COMMIT TRANSACTION
END
RETURN @Result
END

SET QUOTED_IDENTIFIER ON

GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO

ALTER PROCEDURE akCreateAFolderAccount @Id AnAccountId OUTPUT, @CheckRights BIT = 1
AS
BEGIN
/* initialize execution state */
DECLARE @Result INT
SET NOCOUNT ON
SET @Result = 0

BEGIN TRANSACTION
EXECUTE @Result = akCreateACustomAccount @Id OUTPUT, @CheckRights
IF @Result = 0
BEGIN
INSERT
INTO AFolderAccount("Id")
VALUES(@Id)
SET @Result = @@ERROR
END

/* finalize transaction */
fin:
IF @@TRANCOUNT > 0
BEGIN
IF @Result <> 0 ROLLBACK TRANSACTION ELSE COMMIT TRANSACTION
END
RETURN @Result
END

GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO

ALTER PROCEDURE akCreateASubAccount @OwnerFolderId AnAccountId, @Code AnAccountCode, @Id AnAccountId OUTPUT, @CheckRights BIT = 1
AS
BEGIN
DECLARE @Path VARCHAR(8000)
/* initialize execution state */
DECLARE @Result INT
SET NOCOUNT ON
SET @Result = 0

BEGIN TRANSACTION
EXECUTE @Result = akCreateAnAccount @Id OUTPUT, @CheckRights
IF @Result = 0
BEGIN
SET @Path = dbo.akGetInternalAccountPath(@OwnerFolderId, @Code)
INSERT
INTO ASubAccount("Id", "Code", "FolderAccountId", "Path")
VALUES(@Id, @Code, @OwnerFolderId, @Path)
SET @Result = @@ERROR
IF @Result <> 0 GOTO fin
EXECUTE @Result = akUpdateSingleSubAccountAncestors @Id
END
/* finalize transaction */
fin:
IF @@TRANCOUNT > 0
BEGIN
IF @Result <> 0 ROLLBACK TRANSACTION ELSE COMMIT TRANSACTION
END
RETURN @Result
END

GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO

ALTER PROCEDURE akUpdateSingleSubAccountAncestors @AccountId AnAccountId
AS
BEGIN
/* initialize execution state */
DECLARE @Result INT
SET NOCOUNT ON
SET @Result = 0

BEGIN TRANSACTION
INSERT
INTO AnAccountAndItsAncestors(AccountId, AncestorId)
SELECT @AccountId, AAIA.AncestorId FROM AnAccountAndItsAncestors AAIA INNER JOIN ASubAccount SA ON (AAIA.AccountId = SA.FolderAccountId) WHERE SA."Id" = @AccountId
SET @Result = @@ERROR
IF @Result <> 0 GOTO fin

/* finalize transaction */
fin:
IF @@TRANCOUNT > 0
BEGIN
IF @Result <> 0 ROLLBACK TRANSACTION ELSE COMMIT TRANSACTION
END
RETURN @Result
END

GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO

if exists (select * from dbo.sysobjects where id = object_id('"dbo"."akGetInternalAccountPath"') and xtype in ('FN', 'IF', 'TF'))
drop function "dbo"."akGetInternalAccountPath"
GO

CREATE FUNCTION akGetInternalAccountPath(@FolderAccountId AnAccountId, @AccountCode AnAccountCode)
RETURNS VARCHAR(8000)
AS
BEGIN
DECLARE @Result VARCHAR(8000)

SET @Result = dbo.akGetAccountPath(@FolderAccountId) + '\' + @AccountCode

RETURN @Result
END

GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO

if exists (select * from sysobjects where id = object_id('"dbo"."akCreateAnAccount"') and sysstat & 0xf = 4)
drop procedure "dbo"."akCreateAnAccount"
GO

CREATE PROCEDURE akCreateAnAccount @Id AnAccountId OUTPUT, @CheckRights BIT = 1
AS
BEGIN
/* initialize execution state */
DECLARE @Result INT
SET NOCOUNT ON
SET @Result = 0

BEGIN TRANSACTION
EXECUTE @Result = akCreateACustomAccount @Id OUTPUT, @CheckRights
IF @Result = 0
BEGIN
INSERT
INTO AnAccount("Id")
VALUES(@Id)
SET @Result = @@ERROR
END

/* finalize transaction */
fin:
IF @@TRANCOUNT > 0
BEGIN
IF @Result <> 0 ROLLBACK TRANSACTION ELSE COMMIT TRANSACTION
END
RETURN @Result
END

GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

if exists (select * from sysobjects where id = object_id('"AnAccount"') and sysstat & 0xf = 3)
drop table "AnAccount"
GO

CREATE TABLE "AnAccount" (
"Id" "int" NOT NULL ,
CONSTRAINT "XPKAnAccount" PRIMARY KEY CLUSTERED
(
"Id"
) WITH FILLFACTOR = 90,
CONSTRAINT "aca_is_aa" FOREIGN KEY
(
"Id"
) REFERENCES "ACustomAccount" (
"Id"
) ON DELETE CASCADE
)
GO


Я постарался отобрать самый минимум