简单实用的分页存储过程，支持多字段排序

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),        --顺排序字段，支持多个，字段先后顺序与倒排序一致(有重复性字段时，最后加ID排序)
@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 '+@orderASC+') TempTable order by '+@orderDESC+') A  ORDER BY  '+@orderASC
    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

 

 

附：其他实现思路：

1、

CREATE PROCEDURE [dbo].[sp_PageRecordByRowNumber]
@sqlStr NVARCHAR(MAX)='',            --表数据源
@Orders NVARCHAR(200)='',            --排序，直接写排序语句，不要加order by，如intID desc
@intPage INT=1,                        --当前页
@PageSize INT=20,                    --页大小
@TotalRecord INT=0,                    --如果等于0则每次都计算总记录数，大于0则不计算，直接返回
@RetVal VARCHAR(100) = NULL OUTPUT  --结果说明
AS
DECLARE @Rt INT;
DECLARE @Sql NVARCHAR(max);
SET @Sql='SELECT * FROM (SELECT ROW_NUMBER() OVER (ORDER BY '+@Orders+') AS RowNumber,* 
        FROM ('+@Sqlstr+') t ) t1 
        WHERE RowNumber BETWEEN ' + CONVERT(VARCHAR,(@intPage-1)*@PageSize+1)+' AND ' + CONVERT(VARCHAR,@intPage*@PageSize);
EXEC sp_executesql @Sql;

--得到记录总数
IF @TotalRecord = 0
BEGIN
SET @Sql='SELECT @TotalRecord = COUNT(*) FROM ('+@sqlstr+') t';
EXEC sp_executesql @sql, N'@TotalRecord int OUTPUT', @Rt OUTPUT;
END
ELSE
SET @Rt = @TotalRecord;
RETURN @Rt;

2、

select top 5 * 
from t_product 
where id not in (select top (5*1) id from t_product)

 

转载于:https://www.cnblogs.com/pengbp/archive/2012/07/03/2574335.html