spDelete_DiscussionPosts

    CREATE  PROCEDURE [dbo].[spDelete_DiscussionPosts]
(            @PKID int,
            @CurrentUsersID int
)

AS

BEGIN

set nocount on
begin transaction

declare @topicid int
declare @sequence int
declare @maxsequence int
declare @indentlevel int
declare @SiteContentID int
declare @replies int

select @sequence = dp.sequence, @indentlevel = dp.indentlevel, @topicid = dp.topicid, @SiteContentID = SiteContentID, @replies = dt.replies
from discussionposts dp
inner join discussiontopics dt on dt.id = dp.topicid
inner join discussiongroups dg on dg.id = dt.groupid
where dp.id = @PKID

if @sequence is not null
begin
            select @maxsequence = min(sequence)
            from DiscussionPosts
            where TopicID=@TopicID
            and IndentLevel<= @indentlevel
            and sequence > @sequence

            if @maxsequence is null
                        select @maxsequence = max(sequence)
                        from DiscussionPosts
                        where TopicID = @TopicID
            else
                        if @maxsequence > @sequence
                                    set @maxsequence = @maxsequence - 1


            delete from Search
            where SiteContentID = @SiteContentID
            and exists (
                        select *
                        from DiscussionPosts dp
                        where dp.topicid = @topicid
                        and dp.sequence between @sequence and @maxsequence
                        and SearchInt1 = dp.id
            )

            delete from discussionposts
            where topicid = @topicid
            and sequence between @sequence and @maxsequence

            update discussionposts 
            set sequence = sequence - (@maxsequence-@sequence+1)
            where topicid = @topicid and sequence > @sequence

            update discussiontopics
            set replies = @replies - (@maxsequence-@sequence+1)
            where id = @topicid

end

commit transaction

END