分页查询

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};