分页存储过程（一）：分页获得单个表的数据

-------------------------------------------------------------------------
-- 名称(Name)：GetRecordFromSingleTableByPage
-- 功能(Function)：分页获得单个表的数据
-- 书写者(Author)：
-- 日期(Date)：2006年02月15日
-- 输入参数：
/*
    @tblName      nvarchar(255),        -- 表名
    @priKeyName      nvarchar(50),            -- 主键列
    @fldNames     nvarchar(1000),       -- 要取值的字段名,多个字段通过逗号分割
    @PageSize     int = 0,              -- 页尺寸，0表示不需要分页
    @PageIndex    int = 1,              -- 页码，从1开始
    @OrderType    nvarchar(200) = '',   -- 设置排序，'':没有排序要求 0：主键升序 1：主键降序 else：用户自定义排序规则
    @strWhere     nvarchar(2000) = '',  -- 查询条件 (注意: 不要加 where)
*/
--输出参数:
--    @TotalItem int output,    -- 总的记录数
--    @TotalPage int output    -- 总的页数    
-- 说明: 适用于多个字段参与排序的分页查询
-------------------------------------------------------------------------
-- 修改者：
-- 修改日期：
-- 修改说明：
-------------------------------------------------------------------------
ALTER    PROCEDURE [dbo].[GetRecordFromSingleTableByPage]
    @tblName      nvarchar(255),        -- 表名
    @priKeyName      nvarchar(50),        -- 主键列或标示列
    @fldNames     nvarchar(1000),       -- 字段名,多个字段通过逗号分割
    @PageSize     int = 0,              -- 页尺寸
    @PageIndex    int = 1 output,              -- 页码
    @OrderType    nvarchar(200) = '',   -- 设置排序，'':没有排序要求 0：主键升序 1：主键降序 字符串：用户自定义排序规则
    @strWhere     nvarchar(2000) = '',  -- 查询条件 (注意: 不要加 where)
    @TotalItem int output,    
    @TotalPage int output
AS

declare @strByPage   nvarchar(4000)   -- 分页查询语句
declare @strNoPage nvarchar(1500)      -- 不分页查询语句，获得所有符合条件的记录
declare @strTotal nvarchar(1000)      -- 统计符合条件的纪录数
declare @strNonResult nvarchar(1500)  -- 返回空记录的语句
declare @strTmp nvarchar(100)
declare @strOrder nvarchar(200)

if @OrderType is null or @OrderType=''
begin
    set @strOrder = ''
    set @strTmp=''
end
else if @OrderType = '0' -- 降序
begin
    set @strTmp = @priKeyName + '>(select max([' + @priKeyName + ']) from '
    set @strOrder = ' order by ' + @priKeyName + ' asc'
end
else if @OrderType = '1' -- 降序
begin
    set @strTmp = @priKeyName + '<(select min([' + @priKeyName + ']) from '
    set @strOrder = ' order by ' + @priKeyName + ' desc'
end
else -- 用户自定义排序规则
begin
    set @strTmp = ''
    set @strOrder = ' order by ' + @OrderType
end


set @strNonResult = 'select ' + @fldNames + ' from ' + @tblName + ' where 1=2'


if @strWhere is null or @strWhere = '' -- 如果没有额外的查询条件
begin
    set @strTotal = N'select @TotalItem = count(*) from ' + @tblname 
    set @strNoPage = N'select ' + @fldNames + ' from ' + @tblName + @strOrder
end
else
begin
    set @strTotal = N'select @TotalItem = count(*) from ' + @tblname + ' where ' + @strWhere
    set @strNoPage = N'select ' + @fldNames + ' from ' + @tblName + ' where ' + @strWhere + @strOrder
end

-- 取得所有符合查询条件的记录数
exec sp_executeSql @strTotal,N'@TotalItem int output',@TotalItem output
-- 如果没有适合条件的记录时,提供一个空的记录集并退出查询
if @TotalItem = 0
begin
    set @TotalPage = 0
    exec sp_executeSql @strNonResult    
    return 0
end
-- 执行查询，此时记录集不为空
if @PageSize = 0   -- 当不需要分页时
begin
    set nocount off
    set @TotalPage = 0
    exec sp_executeSql @strNoPage
    return
end
else    -- 当需要分页时
begin
    set nocount on
    -- 得到记录的页数，并调整页号，分页从1开始
    set @TotalPage=CEILING(cast(@TotalItem as float)/@PageSize)
    if(@PageIndex>@TotalPage)
        set @PageIndex=@TotalPage
    if(@PageIndex <1)
        set @PageIndex=1
    if @PageIndex =1    -- 如果是第一页
    begin
        if @strWhere=''
            set @strByPage = N'select top ' + cast(@PageSize as varchar) + ' ' + @fldNames + ' from ' + @tblName + @strOrder
        else
            set @strByPage = N'select top ' + cast(@PageSize as varchar) + ' ' + @fldNames + ' from ' + @tblName + ' where ' + @strWhere + @strOrder
    end
    else    -- 以后页
    begin
        
        if (@OrderType='0' or @OrderType='1') -- 按主键升序或降序
        begin
            if @strWhere=''
                set @strByPage = N'select top ' + cast(@PageSize as varchar) + ' ' + @fldnames 
                    + ' from ' + @tblName 
                    + ' where ' + @strTmp
                    + ' (select top ' + cast((@PageIndex-1) * @PageSize as varchar) + ' ' + @priKeyName 
                    + ' from ' + @tblName + @strOrder + ') as tmptbl)'
                    + @strOrder
            else
                set @strByPage = N'select top ' + cast(@PageSize as varchar) + ' ' + @fldnames 
                    + ' from ' + @tblName 
                    + ' where ' + @strTmp
                    + ' (select top '+ cast((@PageIndex-1) * @PageSize as varchar) + ' ' + @priKeyName 
                    + ' from ' + @tblName + ' where ' + @strWhere + @strOrder + ') as tmptbl)'
                    + ' and ' + @strWhere
                    + @strOrder
        end
        else  -- 没有排序规则或者用户自定义规则
        begin 
            if @strWhere=''
                set @strByPage = N'select top ' + cast(@PageSize as varchar) + ' ' + @fldnames 
                    + ' from ' + @tblName 
                    + ' where not exists (select * from '
                    + '    (select top ' + cast((@PageIndex-1) * @PageSize as varchar) + ' * from ' 
                    + @tblName + @strorder + ') as tmpTable '
                    + ' where tmpTable.' + @priKeyName + ' = ' + @tblName +'.' + @priKeyName + ')'
                    + @strorder
            else
                set @strByPage = N'select top ' + cast(@PageSize as varchar) + ' ' + @fldnames 
                    + ' from ' + @tblName 
                    + ' where not exists (select * from '
                    + '    (select top ' + cast((@PageIndex-1) * @PageSize as varchar) + ' * from ' 
                    + @tblName + ' where ' + @strWhere + @strorder + ') as tmpTable '
                    + ' where tmpTable.' + @priKeyName + ' = ' + @tblName + '.' + @priKeyName + ')'
                    + ' and ' + @strWhere
                    + @strorder
        end
    end

end
--print @strByPage
exec sp_executeSql @strByPage
return