CREATE PROCEDURE [dbo].[sp_Forums_GetRepliesByPage]
@TopicID	int,
@PageNumber	int,
@PageSize	int
AS

-- create a temporary table with the columns we are interested in
CREATE TABLE #TempReplies
(
	ID 		int IDENTITY PRIMARY KEY,
	ReplyID		int,
	Message	text COLLATE Cyrillic_General_CI_AS,
	AddedDate	datetime,
	MemberID	int,
	MemberName	varchar( 15 ) COLLATE Cyrillic_General_CI_AS,
	Email		varchar( 50 ) COLLATE Cyrillic_General_CI_AS,
	ShowEmail	bit,
	Signature	varchar( 300 ) COLLATE Cyrillic_General_CI_AS,
	AvatarUrl	varchar( 100 ) COLLATE Cyrillic_General_CI_AS,
	Homepage	varchar( 100 ) COLLATE Cyrillic_General_CI_AS
)

-- fill the temp table with all the replies for the 
-- specified topic retrieved from the v_Forums_Replies view
INSERT INTO #TempReplies
(
	ReplyID,
	Message,
	AddedDate,
	MemberID,
	MemberName,
	Email,
	ShowEmail,
	Signature,
	AvatarUrl,
	Homepage
)
SELECT 
	ReplyID,
	Message,
	AddedDate,
	MemberID,
	MemberName,
	Email,
	ShowEmail,
	Signature,
	AvatarUrl,
	Homepage
FROM 
  v_Forums_Replies WHERE TopicID = @TopicID ORDER BY AddedDate Asc

-- declare two variables to calculate the range of records to extract for the specified page
DECLARE @FromID int
DECLARE @ToID int
-- calculate the first and last ID of the range of replies we need
SET @FromID = ((@PageNumber -  1 ) * @PageSize) +  1 
SET @ToID = @PageNumber * @PageSize

-- select the page of records
SELECT ReplyID, Message, AddedDate, MemberID, MemberName, Email, ShowEmail, Signature, AvatarUrl, Homepage 
	FROM #TempReplies WHERE ID >= @FromID AND ID <= @ToID

GO