I decided to place it here in case I would need such a thing in the future :)
The select statement selects forum-like data model which supports paging as well. (Where comments will be below the main entry and so on)
DECLARE @Level int
DECLARE @CurrentID int
DECLARE @ParentID int
Declare @rowcount int
DECLARE @LastGroupDate DATETIME
DECLARE @LastPageID INT
CREATE TABLE #stack(
ID int ,
ParentID int ,
Level INT,
GroupDate DATETIME
)
CREATE TABLE #results(
N int IDENTITY,
ID int ,
ParentID int ,
Level int
)
DECLARE @Ignore int
DECLARE @LastID int
set @LastGroupDate= getdate()
IF @Page > 1
BEGIN
/* For pages > 1 compute how many records to ignore,
set ROWCOUNT and SELECT ID into @LastID */
SET @Ignore = @PageSize * (@Page-1)
SET ROWCOUNT @Ignore
SELECT @LastGroupDate=groupdate FROM Forums_Messages
WHERE ParentID IS NULL AND Active=1 AND NodeID=@NodeID
ORDER BY GroupDate DESC
END
ELSE
BEGIN
/* For page #1 just set rowcount to pagesize */
SET ROWCOUNT @PageSize
END
SET ROWCOUNT @PageSize
INSERT INTO #stack
SELECT ID,ParentID,1, GroupDate from Forums_Messages where
groupdate<@LastGroupDate and parentID is null and active=1 AND NodeID=@NodeID
order by groupdate desc
/* Set rowcount to @PageSize and
SELECT page for output (note the WHERE clause) */
SET ROWCOUNT 0
SET @Level = 1
WHILE @Level > 0
BEGIN
IF EXISTS (SELECT * FROM #stack WHERE Level=@Level)
BEGIN
SELECT top 1 @CurrentID=id,@ParentId=ParentID
FROM #stack WHERE Level = @Level order by GroupDate desc
INSERT INTO #results VALUES (@CurrentID,@ParentId,@Level)
DELETE FROM #stack WHERE Level = @Level AND ID = @CurrentID
INSERT #stack
SELECT ID,parentID,@level+1, GroupDate FROM Forums_Messages
WHERE parentID = @CurrentID and active=1 order by CreateDate
IF @@ROWCOUNT > 0
SET @Level = @Level + 1
END
ELSE
SELECT @Level = @Level-1
END
SELECT CEILING(COUNT(*)/CAST(@PageSize AS NUMERIC(10,3)))
FROM Forums_Messages
WHERE ParentID IS NULL AND Active=1 AND NodeID=@NodeID
select Forums_Messages.[ID]
,[NodeID]
,Forums_Messages.[ParentID]
,[GroupID]
,[UserID]
,[UserName]
,[UserEmail]
,[IsForumAdmin]
,[IsSiteAdmin]
,[Subject]
,[Body]
,[AttachmentID]
,[Active]
,[Pending]
,[CreateDate]
,[GroupDate]
,[Link1Url]
,[Link1Text]
,[Link2Url]
,[Link2Text]
,[Link3Text]
,[Link3Url] ,
(#results.level-1)*10%300 AS Level,
'<a href="{0}' + CAST([AttachmentID] AS NVARCHAR(10)) + SystemFiles_Files.FileExtension + '" target="_blank">{1}</a>' AS AttachmentLink
from #results
JOIN Forums_Messages ON #results.id = Forums_Messages.id
LEFT JOIN SystemFiles_Files ON SystemFiles_Files.ID=[AttachmentID]
order by #results.N
drop table #results
drop table #stack