SET QUOTED_IDENTIFIER OFF GO SET ANSI_NULLS OFF GO if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[ntm_indent_node]') and OBJECTPROPERTY(id, N'IsProcedure') = 1) drop procedure [dbo].[ntm_indent_node] GO /****** Object: Stored Procedure dbo.ntm_indent_node Script Date: 3/8/2001 10:28:00 AM ******/ /****** Object: Stored Procedure dbo.ntm_indent_node Script Date: 3/7/2001 11:01:59 AM ******/ /** * Stored Procedure: dbo.ntm_indent_node * Creation Date: Jan 1, 2001 * * Ident a node of a Nested Tree. Nodes can only be indented one level * at a time. * * Mar 8, 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 ParentID field. * 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 as unique id instead of UUID so that nodes can have multiple parents. **/ CREATE PROCEDURE ntm_indent_node @ErrorMessage varchar(250) = NULL OUTPUT -- * Error message returned to caller object ,@TreeID uniqueidentifier ,@IndentID uniqueidentifier ,@node_table_name varchar(255) = 'ntm_nodes' ,@switch_to_internal_id bit = 0 ,@str_debug varchar(255) = "NODEBUG" as begin declare @sql_str nvarchar(1000) declare @SummaryInternalID uniqueidentifier declare @indent_nleft int declare @indent_nright int declare @summary_nleft int if @switch_to_internal_id = 1 begin set @sql_str = N' select @IndentID = InternalNodeID from ' + @node_table_name + N' where NodeID = ''' + convert(nvarchar(50), @IndentID) + N''' ' execute sp_executesql @sql_str, N'@IndentID uniqueidentifier out', @IndentID out end begin transaction set @sql_str = N' select @indent_nleft = nleft, @indent_nright = nright from ' + @node_table_name + ' (TABLOCK HOLDLOCK) where TreeID = ''' + convert(nvarchar(50), @TreeID) + ''' and InternalNodeID = ''' + convert(nvarchar(50), @IndentID) + '''' execute sp_executesql @sql_str, N'@indent_nleft int out, @indent_nright int out', @indent_nleft out, @indent_nright out -- the indented node should become the child of the node's leftmost sibling. set @sql_str = N' select @SummaryInternalID = InternalNodeID, @summary_nleft = nleft from ' + @node_table_name + ' (TABLOCK HOLDLOCK) where TreeID = ''' + convert(nvarchar(50), @TreeID) + ''' and nright = ' + convert(nvarchar(50), @indent_nleft) + ' - 1' execute sp_executesql @sql_str, N'@SummaryInternalID uniqueidentifier out, @summary_nleft int out', @SummaryInternalID out, @summary_nleft out -- If no records are found then the node is indented as far as it can go. if @summary_nleft is not null begin set @sql_str = N' update ' + @node_table_name + N' set -- The level of the Buddy Node (summary node) is not changed. ntmLevel = case when nleft = ' + convert(nvarchar(50), @summary_nleft) + ' then ntmLevel else ntmLevel + 1 end -- change the parentID only for the node being indented. ,ParentID = case when InternalNodeID = ''' + convert(nvarchar(50), @IndentID) + ''' then ''' + convert(nvarchar(50), @SummaryInternalID) + ''' else ParentID end -- the nleft value is not changed for the summary task. ,nleft = case when nleft = ' + convert(nvarchar(50), @summary_nleft) + ' then nleft else nleft - 1 end , nright = case when nleft = ' + convert(nvarchar(50), @summary_nleft) + ' then ' + convert(nvarchar(50), @indent_nright) + ' else nright -1 end where TreeID = ''' + convert(nvarchar(50), @TreeID) + N''' and (InternalNodeID = ''' + convert(nvarchar(50), @SummaryInternalID) + ''' or nleft between ' + convert(nvarchar(50), @indent_nleft) + ' and ' + convert(nvarchar(50), @indent_nright) + ')' execute sp_executesql @sql_str if @@Error <> 0 begin set @errormessage = 'Error updating children nodes.' goto TransactionError end end commit transaction return(1) TransactionError: rollback transaction ErrorHandler: return(-1) end GO SET QUOTED_IDENTIFIER OFF GO SET ANSI_NULLS ON GO