CREATE procedure dbo.Forum_WhatsNewGetAll

@ModuleID int,

@NumberOfThread int,

@UserID int,

@FromDate datetime,

@ToDate datetime,

@Filter nvarchar(500)

CREATE TABLE #PageIndex

IndexID int IDENTITY (1, 1) NOT NULL,

PostID int)

DECLARE @sql nvarchar(1000)

SELECT @sql = 'INSERT INTO #PageIndex (PostID)' +

' SELECT Max(PostID) As PostID' +

' FROM Forum_Posts, Forum_Threads, Forum_Forums, Forum_Groups (nolock) ' +

' WHERE ModuleID = ' + CONVERT(varchar, @ModuleID) +

' AND Forum_Groups.GroupID = Forum_Forums.GroupID' +

' AND Forum_Forums.ForumID = Forum_Threads.ForumID' +

' AND Forum_Threads.ThreadID = Forum_Posts.ThreadID' +

' AND Forum_Posts.IsApproved = 1' +

' AND Forum_Posts.IsLocked = 0' +

' AND (Forum_Threads.ForumID NOT IN (SELECT ForumID FROM Forum_PrivateForums) OR' +

' Forum_Threads.ForumID IN (SELECT ForumID FROM Forum_PrivateForums (nolock) ' +

' WHERE RoleID IN (SELECT RoleID FROM UserRoles (nolock) WHERE UserID =' + CONVERT(varchar, @UserID) +

')))' +

@Filter +

' GROUP BY Forum_Posts.ThreadID' +

' ORDER BY Max(Forum_Posts.PostID) DESC'

Print @sql

EXEC(@sql)

SELECT

G.ModuleID,

T.ForumID,

F.Name AS ForumName,

P1.ThreadID,

P1.PostID As LastPostID,

P2.UserID As StartedByUserID,

U2.Alias As StartedByAlias,

P2.Subject As StartedSubject,

P1.Subject As LastSubject,

P1.UserID As LastPostUserID,

U1.Alias As LastPostAlias,

P1.Body As LastPostBody,

P1.CreatedDate As LastCreatedDate

FROM

#PageIndex PageIndex (nolock), Forum_Posts P1 (nolock), Forum_Posts P2 (nolock), Forum_Threads T (nolock), Forum_Forums F (nolock), Forum_Groups G (nolock), Modules M (nolock), Forum_Users U1 (nolock), Forum_Users U2 (nolock)

WHERE

P1.PostID = PageIndex.PostID AND

P1.CreatedDate > @FromDate AND

P1.CreatedDate < @ToDate AND

P1.ThreadID = T.ThreadID AND

P1.ThreadID = P2.PostID AND

P1.UserID = U1.UserID AND

P2.UserID = U2.UserID AND

T.ForumID = F.ForumID AND

F.GroupID = G.GroupID AND

G.ModuleID = M.ModuleID AND

PageIndex.IndexID <= @NumberOfThread

ORDER BY

PageIndex.IndexID