SET QUOTED_IDENTIFIER OFF GO SET ANSI_NULLS OFF GO if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[ntm_deactivate_node]') and OBJECTPROPERTY(id, N'IsProcedure') = 1) drop procedure [dbo].[ntm_deactivate_node] GO /** * Stored Procedure: dbo.ntm_deactivate_node * Creation Date: Feb 20, 2001 * * deactivate a node (and its children) from a Nested Tree. * * Mar 7, 2000 - David Medinets - @ErrorMessage passes errors to calling routine. **/ CREATE PROCEDURE ntm_deactivate_node @ErrorMessage varchar(250) = NULL OUTPUT -- * Error message returned to caller object ,@TreeID uniqueidentifier ,@node_table_name varchar(255) = 'ntm_nodes' ,@deactivateID uniqueidentifier as begin declare @deactivate_nleft int declare @deactivate_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 = [DeactivateID] 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), @deactivateID) + N'''' execute sp_executesql @sql_str, N'@count int out', @count out if @@Error <> 0 begin set @errormessage = 'NTM Deactivate - problem seeking node to be activated.' goto errorhandler end if @count < 1 begin goto theEnd end set @sql_str = N' select @deactivate_nleft = nleft, @deactivate_nright = nright from ' + @node_table_name + N' where TreeID = ''' + convert(nvarchar(50), @TreeID) + ''' and InternalNodeID = ''' + convert(nvarchar(50), @deactivateID) + '''' execute sp_executesql @sql_str, N'@deactivate_nleft int out, @deactivate_nright int out', @deactivate_nleft out, @deactivate_nright out if @@Error <> 0 begin set @errormessage = 'NTM Deactivate - problem finding node to be activated.' goto errorhandler end set @sql_str = N' update ' + @node_table_name + N' set ntmActive = 0 where TreeID = ''' + convert(nvarchar(50), @TreeID) + ''' and nleft between ' + convert(nvarchar(50), @deactivate_nleft) + ' and ' + convert(nvarchar(50), @deactivate_nright) execute sp_executesql @sql_str if @@Error <> 0 begin set @errormessage = 'NTM Deactivate - problem deactivating nodes.' goto errorhandler end theEnd: return(1) TransactionError: rollback transaction ErrorHandler: return(-1) end GO SET QUOTED_IDENTIFIER OFF GO SET ANSI_NULLS ON GO