Lieven Cardoen

/****** 
	Object:  StoredProcedure [dbo].[sp_DeleteTreeStructure]    
	Script Date: 08/17/2008 19:12:16 
******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[sp_DeleteTreeStructure]
(
	@TreeStructureId INT -- Id TreeStructure to 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, 
				@LeftValue = LeftValue, 
				@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_DeleteTreeStructure. 
						Rollback has been executed.'
 
		/**********************************************************/	
 
	END CATCH
END