SQL分页

不是什么高深算法,工作的时候套上,就用,仅此而已.

 

create PROCEDURE [dbo].[PR_GetForumFilterUserList]
(
    @PageIndex INT=1,
    @PageSize INT=20,
    @numRecord INT OUT
)
 AS 
 BEGIN
    SET NOCOUNT ON;

     DECLARE @pageLowerBound INT ;  
    DECLARE @pageUpperBound INT ;  
    DECLARE @indextable TABLE
        (
            _id INT IDENTITY(1, 1),
            [ID] [int],
            [UserID] [bigint] NULL,
            [NickName] [nvarchar](50) NULL,
            [UserIP] [varchar](23) NULL,
            [Amount] [int] NULL,
            [ThreadIDs] [varchar](max) NULL,
            [ThreadContent] [nvarchar](max) NULL,
            [PostTime] [datetime] NULL,
            [type] [int] NULL
        ) ;

    SET @pageLowerBound = ( @pageIndex - 1 ) * @pageSize ;  
    SET @pageUpperBound = @pageLowerBound + @pageSize ; 

    INSERT  INTO @indextable (ID, UserID, NickName, UserIP, Amount, ThreadIDs,
                 ThreadContent, PostTime, [type])
                 SELECT ffu2.ID, ffu2.UserID, ffu2.NickName, ffu2.UserIP,
                        ffu2.Amount, ffu2.ThreadIDs, ffu2.ThreadContent,
                        ffu2.PostTime, ffu2.[type]
                   FROM qidiandb.dbo.ForumFilter_User ffu2

    SELECT @numRecord = COUNT(*) FROM @indextable;

    SELECT ID,UserID,NickName,UserIP,Amount,ThreadIDs,ThreadContent,PostTime,[type]
        FROM @indextable
        WHERE _id > @pageLowerBound 
              AND _id <= @pageUpperBound  
        ORDER BY ID DESC;

    RETURN @@ROWCOUNT
 END