SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS OFF 
GO

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




/**
 * Stored Procedure:	dbo.ntm_activate_node
 * Creation Date:	Feb 20, 2001
 *
 * activate a node (and its children) from a Nested Tree.
 *
 * Mar 7, 2000 - David Medinets - @ErrorMessage passes errors to calling routine.
 **/

CREATE   PROCEDURE ntm_activate_node
	@ErrorMessage varchar(250) = NULL OUTPUT	-- * Error message returned to caller object
	,@TreeID uniqueidentifier
	,@node_table_name varchar(255) = 'ntm_nodes'
	,@activateID uniqueidentifier
as begin
	declare @activate_nleft int
	declare @activate_nright int
	declare @sql_str nvarchar(1000)


	-- let's make sure that the node exists.
	declare @count int
	-- select sum(1) from [table] where TreeID = [TreeID] and InteralNodeID = [activateID]
	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), @activateID) + N''''
	execute sp_executesql @sql_str, N'@count int out', @count out

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

	if @count < 1 begin
		goto theEnd
	end

	set @sql_str = N'
	select 
		@activate_nleft = nleft, @activate_nright = nright 
	from 
		' + @node_table_name + N'
	where 
		TreeID = ''' + convert(nvarchar(50), @TreeID) + '''
		and InternalNodeID = ''' + convert(nvarchar(50), @activateID) + ''''

	execute sp_executesql @sql_str, 
		N'@activate_nleft int out, @activate_nright int out', 
		@activate_nleft out, @activate_nright out


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

	set @sql_str = N'
	update  
		' + @node_table_name + N'
	set
		ntmActive = 1
	where 
		TreeID = ''' + convert(nvarchar(50), @TreeID) + '''
		and nleft between ' + convert(nvarchar(50), @activate_nleft) + ' and ' + convert(nvarchar(50), @activate_nright)

	execute sp_executesql @sql_str

	if @@Error <> 0 begin
		set @errormessage = 'NTM Activate - problem activating nodes.'
		goto TransactionError
	end

	theEnd:
	return(1)

	TransactionError:
	rollback transaction

	ErrorHandler:
	return(-1)
end




GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO