sql2005通用分页存储过程

多表多字段排序

 

--多表多字段排序
Create   PROCEDURE [dbo].[PageSrcList]
 @TableName varchar(350),        --表名
 @Fields varchar(1000) = '*',    --字段名(全部字段为*)
 @OrderField varchar(500),        --排序字段(必须!支持多字段)
 @sqlWhere varchar(1000) = Null,--条件语句(不用加where)
 @pageSize int,                    --每页多少条记录
 @pageIndex int = 1,         --指定当前为第几页
 @distinct VARCHAR(50)=NULL,   --去除重复值，注意只能是一个字段
 @top INT=NULL                --查询TOP,不传为全部
AS 
BEGIN
    Declare @sql nvarchar(4000);
    Declare @totalRecord int;    
    DECLARE @totalPage INT;
    --计算总记录数
 IF (@distinct IS NULL OR @distinct='')
 BEGIN
  IF  (@SqlWhere='' OR  @sqlWhere IS NULL)
    SET  @sql = 'select @totalRecord = count(1) from ' + @TableName
   ELSE
    SET @sql = 'select @totalRecord = count(1) from ' + @TableName + ' where ' + @sqlWhere
 END
 ELSE
 BEGIN
  IF  (@SqlWhere='' OR  @sqlWhere IS NULL)
    SET  @sql = 'select @totalRecord = count(distinct ' + @distinct + ') from ' + @TableName
   ELSE
    SET @sql = 'select @totalRecord = count(distinct ' + @distinct + ') from ' + @TableName + ' where ' + @sqlWhere
 END
    EXEC sp_executesql @sql,N'@totalRecord int OUTPUT',@totalRecord OUTPUT--计算总记录数       
    select @totalRecord
  
    IF(@top>0)
    BEGIN
  --指定TOP 记录
  SET @Fields= 'top ' + CAST(@top AS VARCHAR(20)) + ' ' +  @Fields;
  --如果总记录数超过TOP数,设总记录数为TOP数
  IF(@totalRecord>@top) 
   SET @totalRecord=@top
 END
  
    --计算总页数
    --SELECT @totalPage=CEILING((@totalRecord+0.0)/@PageSize)
    --SELECT @totalRecord AS 'fldtotalRecord',@totalPage AS 'fldTotalPage'
 
 IF (@distinct IS NULL OR @distinct='')
 BEGIN
  IF (@SqlWhere='' or @sqlWhere IS NULL)
   SET @sql = 'Select * FROM (select ' + @Fields + ',ROW_NUMBER() Over(order by ' + @OrderField + ') as rowId from ' + @TableName
  ELSE
   SET @sql = 'Select * FROM (select ' + @Fields + ',ROW_NUMBER() Over(order by ' + @OrderField + ') as rowId from ' + @TableName + ' where ' + @SqlWhere    
 END
 ELSE
 BEGIN
  IF (@SqlWhere='' or @sqlWhere IS NULL)
   SET @sql = 'Select * FROM (select ' + @Fields + ',ROW_NUMBER() Over(order by ' + @OrderField + ') as rowId from ' + @TableName
  ELSE
   SET @sql = 'Select * FROM (select ' + @Fields + ',ROW_NUMBER() Over(order by ' + @OrderField + ') as rowId from ' + @TableName + ' where ' + @SqlWhere    
  SET @sql=@sql + ' GROUP BY ' + @distinct;
 END
    
     
    --处理页数超出范围情况 

    IF @PageIndex<=0 
        SET @pageIndex = 1
    
   
 
     --处理开始点和结束点
    DECLARE @StartRecord INT 
    DECLARE @EndRecord int
    
    SET @StartRecord = (@pageIndex-1)*@PageSize + 1
    SET @EndRecord = @StartRecord + @pageSize - 1
 
     --继续合成sql语句
    SET @sql = @sql + ') as tempTable where rowId >=' + CONVERT(VARCHAR(50),@StartRecord) + ' and rowid<= ' +  CONVERT(VARCHAR(50),@EndRecord)
    Exec(@sql)
end 

 

单表

 

 

Create PROCEDURE [dbo].[PageSrc]
@datasrc nvarchar(500)
,@orderBy nvarchar(200)
,@fieldlist nvarchar(200) = '*'
,@filter nvarchar(400) = ''
,@pageNum int = 1
,@pageSize int = NULL
AS
--SET NOCOUNT ON
DECLARE
     @STMT nvarchar(max)         -- SQL to execute
    ,@recct int                  -- total # of records (for GridView paging interface)

IF LTRIM(RTRIM(@filter)) = '' SET @filter = '1 = 1'
IF @pageSize IS NULL BEGIN
    SET @STMT = 'SELECT   ' + @fieldlist +
                 'FROM     ' + @datasrc +
                 'WHERE    ' + @filter +
                 'ORDER BY ' + @orderBy
    EXEC (@STMT)                 -- return requested records
END ELSE BEGIN
    SET @STMT = 'SELECT   @recct = COUNT(*)
                  FROM     ' + @datasrc + '
                  WHERE    ' + @filter
    EXEC sp_executeSQL @STMT, @params = N'@recct INT OUTPUT', @recct = @recct OUTPUT
    SELECT @recct AS recct       -- return the total # of records

    DECLARE
      @lbound int,
      @ubound int

    SET @pageNum = ABS(@pageNum)
    SET @pageSize = ABS(@pageSize)
    IF @pageNum < 1 SET @pageNum = 1
    IF @pageSize < 1 SET @pageSize = 1
    SET @lbound = ((@pageNum - 1) * @pageSize)
    SET @ubound = @lbound + @pageSize + 1
    IF @lbound >= @recct BEGIN
      SET @ubound = @recct + 1
      SET @lbound = @ubound - (@pageSize + 1) -- return the last page of records if          

                                     -- no records would be on the
                                              -- specified page
    END
    SET @STMT = 'SELECT ' + @fieldlist + '
                  FROM    (
                            SELECT ROW_NUMBER() OVER(ORDER BY ' + @orderBy + ') AS row, *
                            FROM    ' + @datasrc + '
                            WHERE   ' + @filter + '
                          ) AS tbl
                  WHERE
                          row > ' + CONVERT(varchar(9), @lbound) + ' AND
                          row < ' + CONVERT(varchar(9), @ubound)
    EXEC (@STMT)                 -- return requested records
END