SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS OFF 
GO

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


/**
 * Stored Procedure:	dbo.ntm_create_node_table
 **/

CREATE PROCEDURE ntm_create_node_table
	@node_table_name varchar(255) = 'ntm_nodes'
as
begin

	declare @sql_str varchar(8000)

	if exists (select * from dbo.sysobjects where id = object_id(@node_table_name) and OBJECTPROPERTY(id, N'IsUserTable') = 1) begin
		SET @sql_str = 'drop table ' + @node_table_name
		EXECUTE (@sql_str)
		if @@Error <> 0 begin
			goto errorhandler
		end
	end

	SET @sql_str = '
	CREATE TABLE ' + @node_table_name + ' (
		-- The TreeID allows multiple trees in the ntm_nodes
		-- table. So you could have two wholely separate menu
		-- structures, for example.
		TreeID            uniqueidentifier NOT NULL
		-- The NodeID is a foreign key to locate details about
		-- the node.
		,NodeID           uniqueidentifier NOT NULL
		-- The InternalNodeID facilitates multiple parent nodes.
		-- When a node has multiple parents, it is has more than
		-- one record in the ntm_nodes table. Therefore, we need
		-- another way to uniquely identify each record.
		,InternalNodeID   uniqueidentifier UNIQUE NOT NULL
		-- The outdent_node procedure needs to know the parent
		-- node of the node to be outdented. See the comments at the
		-- end of this file. NULLs are allowed because the root of the
		-- tree is represented by null.
		,ParentID         uniqueidentifier NULL
		-- The node name is only here so that testing the code
		-- is easy. In production systems, it would not be needed.
		,NodeName         varchar(255)     NOT NULL
		-- The level (or height or depth depending on your point of view)
		-- is the number of edges between the current node and the root, where
		-- the larger the level number, the farther away the node is from the
		-- root.
		,ntmLevel         int              NOT NULL
		-- Deactivating a node is an alternative to deletion. Inactive
		-- nodes are not returned by any query. 
		,ntmActive        bit              NOT NULL DEFAULT (1)
		-- The nleft and nright fields are the heart of the Nested Tree Model.
		-- Look elsewhere an explanation of them. Ye''ll get no further help
		-- here.
		,nleft            int              NOT NULL 
		,nright           int              NOT NULL 
		,CHECK (nleft > 0)
		,CHECK (nright > 0)
		,CONSTRAINT ' + @node_table_name + '_ORDER_OKAY CHECK (nleft < nright)
	)'

	EXECUTE (@sql_str)

	if @@Error <> 0 begin
		goto errorhandler
	end

	SET @sql_str = '
	CREATE UNIQUE CLUSTERED INDEX IX_' + @node_table_name + ' ON ntm_nodes(TreeID, nleft) WITH  STATISTICS_NORECOMPUTE'
	if @@Error <> 0 begin
		goto errorhandler
	end

	/* return the success status code */
	return(1)

	ErrorHandler:

		Print '*** Error While Creating NTM_NODES Table. ***'
		/* return the error status code */
		return(-1)

end

-- Why the ParentID field is needed:
--
-- 	In order to find the parent of a given node the following SQL can
--	be used. It's obviously not as efficient as storing the ParentID
--	directly in the record.
--
-- 	select top 1 
--		@parent_nleft = nleft, @parent_nright = nright
--	from 
--		ntm_nodes 
--	where 
--		TreeID = @TreeID 
--		and nleft < @outdent_nleft 
--		and nright > @outdent_nright
--	order by 
--		nleft desc




GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO