分页查询

if   exists  ( select   *   from  dbo.sysobjects  where  id  =   object_id (N ' [dbo].[GetPaginationDataByKey] ' )  and   OBJECTPROPERTY (id, N ' IsProcedure ' )  =   1 )
drop   procedure   [ dbo ] . [ GetPaginationDataByKey ]
GO

SET  QUOTED_IDENTIFIER  ON  
GO
SET  ANSI_NULLS  OFF  
GO





/*


通用分页查询存储过程(按唯一键分页)
 

用法:

exec GetPaginationDataByKey @nvcharTable,@nvcharKeyField,@nvcharWhere,@nvcharSort,
    @bitNoPage,@intPageSize,@intPageNo,@bitReturnAllOnFail,@intTotalCount output

    
DECLARE @intTotalCountReturn int

exec GetPaginationDataByKey 'ForumTopic','ID','',' ReplyCount DESC',
    0,5,2,0,@intTotalCount = @intTotalCountReturn output

PRINT @intTotalCountReturn

*/


CREATE   PROCEDURE  dbo.GetPaginationDataByKey
(
@nvcharTable   nvarchar ( 500 ),             -- 要查询的表名或视图名称
@nvcharKeyField   nvarchar ( 200 ),             -- 表或视图中唯一标识记录的字段名称
@nvcharWhere   nvarchar ( 2000 ),             -- Where子句
@nvcharSort   nvarchar ( 2000 ),             -- ORDER BY子句
@bitNoPage   bit ,                     -- 是否需要分页返回        0:是    1:否
@intPageSize   int ,                 -- 每页数量
@intPageNo   int ,                     -- 当前页码
@bitReturnAllOnFail   bit ,                 -- 当参数不合法时,是否所有数据    1:是    0:否
@intTotalCount   int  output             -- 所有符合条件的记录数量
)
AS

SET  XACT_ABORT  ON
SET  NOCOUNT  ON

-- 修订表名
DECLARE   @nvcharTableNew   nvarchar ( 500 )
IF (  @nvcharTable   <>   '' ) 
BEGIN
     SET   @nvcharTableNew   =   '  [ '   +    @nvcharTable    +   ' ] '
END
ELSE
BEGIN
     SET   @nvcharTableNew   =   ''
END

-- 修订Where语句
DECLARE   @nvcharWhereNew   nvarchar ( 2000 )
IF ( @nvcharWhere <>   '' ) 
     SET   @nvcharWhereNew   =   '  WHERE (  '   +    @nvcharWhere    +   '  )  '
ELSE
     SET   @nvcharWhereNew   =   '  WHERE 1 = 1  '

-- 修订排序语句
DECLARE   @nvcharSortNew   nvarchar ( 2000 )
IF ( @nvcharSort    <>   '' )
BEGIN
     SET   @nvcharSortNew   =   '  ORDER BY  '   +    @nvcharSort  
END
ELSE
BEGIN
     SET   @nvcharSortNew   =   ''
END

-- 修订关键字段
DECLARE   @nvcharKeyFieldNew   nvarchar ( 200 )
IF ( @nvcharKeyField   <>   '' )
BEGIN
     SET   @nvcharKeyFieldNew   =   ' [ '   +   @nvcharKeyField   +   ' ] '
END
ELSE
BEGIN
     SET   @nvcharKeyFieldNew   =   ''
END


-- 记录总数
DECLARE   @QueryCountString   NVARCHAR ( 4000 )
SET   @QueryCountString   =   ' SELECT @intTotal = COUNT(*) FROM  '   +   @nvcharTableNew   +   @nvcharWhereNew
execute  sp_executesql  @QueryCountString ,
            N ' @nvcharTableNew nvarchar(2000), @nvcharWhereNew nvarchar(2000),@intTotal int output ' ,
                    @nvcharTableNew ,  @nvcharWhereNew , @intTotalCount  output

-- 如果不分页时返回所有
IF ( @bitNoPage   =   1 )
BEGIN
     EXEC ( ' SELECT * FROM  '   +   @nvcharTableNew   +   @nvcharWhereNew   +   @nvcharSortNew )
     RETURN
END

-- 参数不合法按要求返回空数据集或所有数据
IF ( @intPageSize   <=   0   OR   @intPageNo   <   1   OR   @nvcharSortNew   =   ''   OR   @nvcharKeyFieldNew   =   '' )
BEGIN
     IF ( @bitReturnAllOnFail   =   0 )
     BEGIN
         EXEC ( ' SELECT Top 0 * FROM  '   +   @nvcharTableNew )
     END
     ELSE
     BEGIN
         EXEC ( ' SELECT * FROM  '   +   @nvcharTableNew   +   @nvcharWhereNew   +   @nvcharSortNew )
     END

     RETURN  
END

-- 查询语句
DECLARE   @nvcharSql   nvarchar ( 4000 )
     SET   @nvcharSql   =  
     '  SELECT Top  '   +   CONVERT ( nvarchar ( 200 ), @intPageSize )  +   '  * FROM  '   +   @nvcharTableNew   +    @nvcharWhereNew
     +   '  AND  '
     +   @nvcharKeyFieldNew   +   '  NOT IN ( '  
     +   '  SELECT Top  '   +   CONVERT ( nvarchar ( 200 ), @intPageSize   *  ( @intPageNo   -   1 ))  +   '   '   +   @nvcharKeyFieldNew   +   '  FROM  '   +   @nvcharTableNew   +   @nvcharWhereNew   +   @nvcharSortNew
     +   '  )  '
     +   @nvcharSortNew

execute  sp_executesql  @nvcharSql ,
    N ' @intPageSize int,@nvcharTableNew nvarchar(500),@nvcharWhereNew nvarchar(2000), @nvcharKeyFieldNew nvarchar(200),@intPageNo int,@nvcharSortNew nvarchar(2000) ' ,
     @intPageSize , @nvcharTableNew , @nvcharWhereNew , @nvcharKeyFieldNew , @intPageNo , @nvcharSortNew

SET  NOCOUNT  OFF



GO
SET  QUOTED_IDENTIFIER  OFF  
GO
SET  ANSI_NULLS  ON  
GO

-- SQL 2005

WITH  TempQuery  AS
(
     SELECT  { 0 }, ROW_NUMBER()  OVER  ( ORDER   BY  { 3 })  AS   ' RowNumberForSplit '
     FROM  { 1 }
     WHERE   1   =   1  { 2 }
)
SELECT   *  
FROM  TempQuery 
WHERE  RowNumberForSplit  BETWEEN  { 4 }  AND  { 5 };