Help! How to get common strings of numbers from my SQL Server swap routine?

Question

Good. I have a table in my SQL Server database that stores comments. My desire is to be able to leaf through entries using the [Back], [Next], page and [Recent] entries in my data list. I decided that the most efficient way was to use a stored procedure that returns only a certain number of rows in a certain range. Here is what I came up with

@PageIndex INT, 
@PageSize INT,
@postid int


AS
 SET NOCOUNT ON  
 begin

WITH tmp AS ( 
SELECT comments.*, ROW_NUMBER() OVER (ORDER BY dateposted ASC)  AS  Row
    FROM    comments
    WHERE     (comments.postid = @postid))

SELECT tmp.*
FROM tmp
WHERE Row between 

(@PageIndex - 1) * @PageSize + 1 and @PageIndex*@PageSize

end

RETURN 

, [Next] [Back] . ( ), [Last] . , select i.e

  WITH tmp AS ( 
    SELECT comments.*, ROW_NUMBER() OVER (ORDER BY dateposted ASC)  AS  Row
        FROM    comments
        WHERE     (comments.postid = @postid))
set @TotalRows = @@rowcount

@@rowcount . . * .

.

Answer 1

, , , :

• ,

- :

    ...
    , @Count int OUTPUT
AS 
Select @Count = (
                Select Count(*)
                From comments
                Where comments.postid = @postid
                    And Col1 = ... And Col2 = ...
                )

With NumberedResults As
    (
    Select ...
        , ROW_NUMBER() OVER( ORDER BY dateposted ASC ) As Num
    From comments
    Where Col1 = ... And Col2 = ...
    )
Select ...
From NumberedResults
Where Num Between ( @PageIndex - 1 ) * @PageSize + 1 and @PageIndex * @PageSize

, , .

. -. , . , .

With NumberedResults As
    (
    Select ...
        , ROW_NUMBER() OVER( ORDER BY dateposted ASC ) As Num
    From comments
    Where Col1 = ... And Col2 = ...
    )
Select ...
    , ( Select Count(*) From NumberedResults ) As TotalCount
From NumberedResults
Where Num Between ( @PageIndex - 1 ) * @PageSize + 1 and @PageIndex * @PageSize

- , temp

    ...
    , @TotalCount int OUTPUT
AS

Declare @PagedResults Table (
                            Col1 ...
                            , ...
                            , TotalCount int
                            )
With NumberedResults As
    (
    Select ...
        , ROW_NUMBER() OVER( ORDER BY dateposted ASC ) As Num
    From comments
    )
Insert @PagedResults( Col1...., TotalCount )
Select ...
    , ( Select Count(*) From NumberedResults ) As TotalCount
From NumberedResults
Where Num Between ( @PageIndex - 1 ) * @PageSize + 1 and @PageIndex * @PageSize

Set @TotalCount = ( Select TOP 1 TotalCount From @PagedResults )

Select ...
From @PagedResults

Answer 2

, :

SELECT TotalRows = COUNT(*)
FROM comments
WHERE comments.postid = @postid

- .