/******
Object:  StoredProcedure [dbo].[sp_DeleteTreeStructureChildren]
Script Date: 08/17/2008 19:12:16
******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[sp_DeleteTreeStructureChildren]
(
@TreeStructureId int -- Id TreeStructure which children should be deleted
)
AS
BEGIN
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;

DECLARE @TranName NVARCHAR(MAX)
SELECT @TranName = 'DeleteTreeStructure';
BEGIN TRANSACTION @TranName

BEGIN TRY

DECLARE @RightValue int
DECLARE @LeftValue int
DECLARE @Width int
DECLARE @TreeId int

SELECT	@RightValue = (RightValue - 1),
@LeftValue = (LeftValue + 1),
@Width = (RightValue - LeftValue) - 1,
@TreeId = TreeId
FROM	TreeStructures
WHERE	(TreeStructureId = @TreeStructureId)

DELETE
FROM	TreeStructures
WHERE	(LeftValue BETWEEN @LeftValue AND @RightValue)
AND TreeId = @TreeId

UPDATE	TreeStructures
SET 	RightValue = RightValue - @Width
WHERE	RightValue > @RightValue
AND TreeId = @TreeId

UPDATE	TreeStructures
SET 	LeftValue = LeftValue - @Width
WHERE	LeftValue > @RightValue
AND TreeId = @TreeId

COMMIT TRANSACTION @TranName

END TRY

BEGIN CATCH

/*
If an error occured we Roll Back the transaction
and raise an error that VS can catch
*/

ROLLBACK TRANSACTION @TranName
RAISERROR 50001 'Error in sp_DeleteTreeStructureChildren.
Rollback has been executed.'

/**********************************************************/

END CATCH
END