简单易用的分页存储过程(只按唯一性字段排序)

USE [test]
GO
/****** 对象:  StoredProcedure [dbo].[PublicSplitPage_sp]    脚本日期: 06/29/2012 16:00:35 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE  PROCEDURE [dbo].[PublicSplitPage_sp]
@TableName varchar(8000),            --表名
@SqlStr    varchar(8000)=null,    --查询语句
@Condition varchar(8000),    --查询条件
@PageIndex int,                --页所引，从0开始
@PageSize int,                --分页大小
@orderDESC varchar(100),    --倒排序字段，支持多个字段
@orderASC varchar(100),        --顺排序字段，支持多个，字段先后顺序与倒排序一致
@PKField  varchar(50)='*',    --索引字段
@SearField varchar(1000),    --查询字段
@RecordCount int out        --返回记录数
AS
if @SqlStr is not null and @SqlStr<>''
begin
    exec(@SqlStr)
    select @RecordCount=@@ROWCOUNT
end 
else
begin
    declare @iTop int,@tmpTop int
    declare @vSQL nvarchar(2000)
    set @vSQL = N'select @count=Count('+@PKField+') from ' + @TableName + ' where ' + @Condition
    exec sp_ExecuteSQL @vSQL, N'@count int output', @RecordCount output    
    set @iTop=@PageSize
    set @tmpTop=@RecordCount-(@PageSize*(@PageIndex-1))
    if @RecordCount<@PageSize*@PageIndex
    begin
        set @iTop=@tmpTop
    end
    
    DECLARE @sqlStr1 varchar(8000)
    IF @PageIndex = 1
        SET @sqlStr1 = 'SELECT TOP ' + STR(@PageSize) + '  '+@SearField+'  FROM '+@TableName+' WHERE '+@Condition+' ORDER BY  '+@orderDESC
    ELSE if @PageIndex<@RecordCount/(2*@PageSize)    -- 此处可以加优化，从中间页往两边
        SET @sqlStr1 = 'SELECT TOP ' + STR(@PageSize) + '  * FROM (select top  ' + STR(@PageSize) + '  * from (SELECT TOP ' 
             + STR(@PageSize*@PageIndex) + '  '+@SearField+'  FROM '+@TableName
             +' WHERE '+@Condition+'  ORDER BY '+@orderDESC+') TempTable order by '+@orderASC+') A  ORDER BY  '+@orderDESC
    else
        SET @sqlStr1 = 'SELECT TOP ' + STR(@iTop) + '  * FROM (SELECT TOP ' 
             + STR(@tmpTop) + '  '+@SearField+'  FROM '+@TableName
             +' WHERE '+@Condition+'  ORDER BY '+@orderASC+') TempTable  ORDER BY  '+@orderDESC
    print(@sqlStr1)
    EXEC(@sqlStr1)
end

转载于:https://www.cnblogs.com/pengbp/archive/2012/06/29/2570139.html