Stored Procedure Properties

Name	Value
Owner	dbo
Created	2004-05-31
Startup	False
Encrypted	False

Creation Options

Name	Value
QUOTED_IDENTIFIER	OFF
ANSI_NULLS	OFF

Parameters

Name	DataType	Length	Type
@ForumID	int	4	INPUT
@ReplyToPostID	int	4	INPUT
@Subject	nvarchar	512	INPUT
@UserName	nvarchar	100	INPUT
@Body	ntext	16	INPUT
@IsLocked	bit	1	INPUT
@Pinned	datetime	8	INPUT

Total: 7 parameter(s)

SQL

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS OFF 
GO




CREATE             PROCEDURE forums_AddPost 
(
	@ForumID int,
	@ReplyToPostID int, 
	@Subject nvarchar(256),
	@UserName nvarchar(50),
	@Body ntext,
	@IsLocked bit,
	@Pinned datetime
) AS
DECLARE @MaxSortOrder int
DECLARE @ParentLevel int
DECLARE @ThreadID int
DECLARE @ParentSortOrder int
DECLARE @NextSortOrder int
DECLARE @NewPostID int
DECLARE @ApprovedPost bit
DECLARE @ModeratedForum bit
DECLARE @IsPinned bit


-- Is the post pinned?
IF @Pinned IS NULL
BEGIN
	SET @IsPinned = 0
	SET @Pinned = GetDate()
END
ELSE
	SET @IsPinned = 1

-- Is this forum moderated?
IF @ForumID = 0 AND @ReplyToPostID <> 0
	-- we need to get the forum ID
	SELECT @ForumID = ForumID FROM Posts (nolock) WHERE PostID = @ReplyToPostID
SELECT @ModeratedForum = Moderated FROM Forums (nolock) WHERE ForumID = @ForumID


-- Determine if this post will be approved
-- if the forum is NOT moderated, then the post will be approved
SET NOCOUNT ON
BEGIN TRAN
IF @ModeratedForum = 0
	SELECT @ApprovedPost = 1
ELSE
  BEGIN
	-- ok, this is a moderated forum.  Is this user trusted?  If he is, then the post is approved ; else it is not
	IF (SELECT Trusted FROM Users (nolock) WHERE UserName = @UserName) = 1
		SELECT @ApprovedPost = 1
	ELSE
		SELECT @ApprovedPost = 0
  END
IF @ReplyToPostID = 0 -- New Post
  BEGIN

    -- Do INSERT into Posts table
    INSERT 
	Posts ( ForumID, ThreadID, ParentID, PostLevel, SortOrder, Subject, PinnedDate, IsPinned, UserName, Approved, Body, IsLocked )
    VALUES 
	(@ForumID, 0, 0, 1, 1, @Subject, @Pinned, @IsPinned, @UserName, @ApprovedPost, @Body, @IsLocked)

    -- Get the new post id
    SELECT 
	@NewPostID = @@IDENTITY

    -- update posts with the new post id
    UPDATE 
	Posts
    SET 
	ThreadID = @NewPostID,
        ParentID = @NewPostID
    WHERE 
	PostID = @NewPostID

   -- do we need to track the threads for this user?
   SELECT @ThreadID = @NewPostID

  END
ELSE -- @ReplyToID <> 0 means reply to an existing post
  BEGIN
    -- Get Post Information for what we are replying to
    SELECT 
	   @ParentLevel = PostLevel,
           @ThreadID = ThreadID,
           @ParentSortOrder = SortOrder,
           @ForumID = ForumID
    FROM 
	   Posts
    WHERE 
           PostID = @ReplyToPostID

    -- Is there another post at the same level or higher
    IF EXISTS (SELECT * 
               FROM Posts 
               WHERE PostLevel <= @ParentLevel 
               AND SortOrder > @ParentSortOrder
               AND ThreadID = @ThreadID )
      BEGIN

        -- Find the next post at the same level or higher
        SELECT 
		@NextSortOrder = Min(SortOrder)
        FROM 
		Posts
        WHERE 
		PostLevel <= @ParentLevel 
        	AND SortOrder > @ParentSortOrder
	        AND ThreadID = @ThreadID

        -- Move the existing posts down
	UPDATE 
		Posts
        SET 
		SortOrder = SortOrder + 1
        WHERE 
		ThreadID = @ThreadID
	        AND SortOrder >= @NextSortOrder

        --  And put this one into place
        INSERT 
		Posts (ForumID, ThreadID, ParentID, PostLevel, SortOrder, Subject, PostDate, IsPinned, UserName, Approved, Body, IsLocked )
        VALUES 
		(@ForumID, @ThreadID, @ReplyToPostID, @ParentLevel + 1, @NextSortOrder, @Subject, @Pinned, @IsPinned, @UserName, @ApprovedPost, @Body, @IsLocked )

	-- Clean up PostsRead
	DELETE PostsRead WHERE PostID = @ThreadID AND UserName != @UserName


      END
   ELSE -- There are no posts at this level or above
     BEGIN

    	-- Find the highest sort order for this parent
    	SELECT 
		@MaxSortOrder = MAX(SortOrder)
    	FROM 
		Posts
    	WHERE 
		ThreadID = @ThreadID

	-- Insert the new post
    	INSERT 
		Posts (ForumID, ThreadID, ParentID, PostLevel, SortOrder, Subject, PinnedDate, IsPinned, UserName, Approved, Body, IsLocked )
    	VALUES 
		(@ForumID, @ThreadID, @ReplyToPostID, @ParentLevel + 1, @MaxSortOrder + 1, @Subject, @Pinned, @IsPinned, @UserName, @ApprovedPost, @Body, @IsLocked )


	-- Clean up PostsRead
	DELETE PostsRead WHERE PostID = @ThreadID AND UserName != @UserName

     END 

     SELECT 
	@NewPostID = @@IDENTITY FROM Posts

     -- if this message is approved, update the thread date
     IF @ApprovedPost = 1
	UPDATE 
		Posts 
	SET 
		ThreadDate = getdate()
	WHERE 
		ThreadID = @ThreadID
  END

  -- update the users tracking for the new post (if needed)
  DECLARE @TrackThread bit
  SELECT 
	@TrackThread = TrackYourPosts 
  FROM 
	Users (nolock) 
  WHERE 
	UserName = @UserName

  IF @TrackThread = 1
	-- if a row already exists to track this thread for this user, do nothing - otherwise add the row
	IF NOT EXISTS(SELECT ThreadID FROM ThreadTrackings (nolock) WHERE ThreadID = @ThreadID AND UserName = @UserName)
		INSERT INTO ThreadTrackings (ThreadID, UserName)
		VALUES(@ThreadID, @UserName)
COMMIT TRAN
BEGIN TRAN

 -- Update the user's post count
 DECLARE @PostCount int
 -- Get the current number of posts
 SELECT @PostCount = TotalPosts FROM Users WHERE UserName = @UserName
 -- Update value
 SET @PostCount = @PostCount + 1
 UPDATE Users SET TotalPosts = @PostCount WHERE UserName = @UserName 

 -- Update the forum statitics
 IF @ApprovedPost = 1
   exec Statistics_UpdateForumStatistics @ForumID, @ThreadID, @NewPostID

 -- Clean up unnecessary columns in forumsread
 exec Maintenance_CleanForumsRead @ForumID


COMMIT TRAN
SET NOCOUNT OFF
SELECT PostID = @NewPostID







GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO