SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS OFF 
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[ntm_delete_node]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[ntm_delete_node]
GO


/****** Object:  Stored Procedure dbo.ntm_delete_node    Script Date: 3/8/2001 11:20:27 AM ******/

/****** Object:  Stored Procedure dbo.ntm_delete_node    Script Date: 3/7/2001 4:55:06 PM ******/

/****** Object:  Stored Procedure dbo.ntm_delete_node    Script Date: 3/7/2001 11:01:44 AM ******/


/**
 * Stored Procedure:	dbo.ntm_delete_node
 * Creation Date:	Jan 1, 2001
 *
 * delete a node (and its children) from a Nested Tree.
 *
 * Mar  7, 2000 - David Medinets - Added @switch_to_internal_id so that applications where
 *					multiple parents aren't needed can ignore the 
 *					InternalNodeID value.
 * Mar 7, 2000 - David Medinets - @ErrorMessage passes errors to calling routine.
 * Feb 8, 2000 - David Medinets - Modifed routine to use dynamic table name
 * Feb 7, 2000 - David Medinets - Modifed routine to use InternalNodeID 
 * Feb 7, 2000 - David Medinets - Modifed routine to support multple trees in the ntm_nodes table.
 * Feb 6, 2000 - David Medinets - modified routine to use nleft value to specify node instead of UUID.
 **/

CREATE     PROCEDURE ntm_delete_node
	@ErrorMessage varchar(250) = NULL OUTPUT	-- * Error message returned to caller object
	,@TreeID uniqueidentifier
	,@node_table_name varchar(255) = 'ntm_nodes'
	,@switch_to_internal_id bit = 0
	,@DeleteID uniqueidentifier
as begin
	declare @delete_nleft int
	declare @delete_nright int
	declare @range int
	declare @sql_str nvarchar(1000)

	if @switch_to_internal_id = 1 begin
		set @sql_str = N'
		select
			@DeleteID = InternalNodeID
		from
			' + @node_table_name + N'
		where
			NodeID = ''' + convert(nvarchar(50), @DeleteID) + N'''
		'
		execute sp_executesql @sql_str, N'@DeleteID uniqueidentifier out', @DeleteID out
	end


	declare @count int
	-- select sum(1) from [table] where TreeID = [TreeID] and InteralNodeID = [BuddyID]
	set @sql_str = N'select @count = sum(1) from ' + @node_table_name + N' where TreeID = ''' + convert(nvarchar(50), @TreeID) + N''' and InternalNodeID = ''' + convert(nvarchar(50), @DeleteID) + N''''
	execute sp_executesql @sql_str, N'@count int out', @count out

	if @@Error <> 0 begin
		set @errormessage = 'NTM Delete - problem seeking node to be deleted.'
		goto errorhandler
	end

	if @count < 1 begin
		goto theEnd
	end

	set @sql_str = N'
	select 
		@delete_nleft = nleft, @delete_nright = nright 
	from 
		' + @node_table_name + N'
	where 
		TreeID = ''' + convert(nvarchar(50), @TreeID) + '''
		and InternalNodeID = ''' + convert(nvarchar(50), @DeleteID) + ''''

	execute sp_executesql @sql_str, 
		N'@delete_nleft int out, @delete_nright int out', 
		@Delete_nleft out, @delete_nright out

	if @@Error <> 0 begin
		set @errormessage = 'NTM Delete - problem finding node to be deleted.'
		goto errorhandler
	end

	-- determine how much the numerical gap needs to be closed.
	set @range = @delete_nright - @delete_nleft + 1

	begin transaction

	set @sql_str = N'
	delete from 
		' + @node_table_name + N'
	where 
		TreeID = ''' + convert(nvarchar(50), @TreeID) + '''
		and nleft between ' + convert(nvarchar(50), @delete_nleft) + ' and ' + convert(nvarchar(50), @delete_nright)

	execute sp_executesql @sql_str

	if @@Error <> 0 begin
		set @errormessage = 'NTM Delete - problem deleting nodes.'
		goto errorhandler
	end

	-- close the gap.
	set @sql_str = N'
	update 
		' + @node_table_name + N'
	set 
		nleft = (case when nleft > ' + convert(nvarchar(50), @delete_nleft) + ' then nleft - ' + convert(nvarchar(50), @range) + ' else nleft end) 
		,nright = (case when nright > ' + convert(nvarchar(50), @delete_nright) + ' then nright - ' + convert(nvarchar(50), @range) + ' else nright end) 
	where 
		TreeID = ''' + convert(nvarchar(50), @TreeID) + '''
		and ((nleft > ' + convert(nvarchar(50), @delete_nleft) + ') OR (nright > ' + convert(nvarchar(50), @delete_nright) + '))'

	execute sp_executesql @sql_str

	if @@Error <> 0 begin
		set @errormessage = 'NTM Delete - problem updating nodes.'
		goto errorhandler
	end

	commit transaction

	theEnd:
	return(1)


	TransactionError:
	rollback transaction

	ErrorHandler:
	return(-1)
end






GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO