SqlServer 数据库 分页存储过程

创建存储过程:

   

-------分页存储过程 --rabtor 修改完善--  

/*
    @tblName      varchar(255),       -- 表名
    @fldName      varchar(255),       -- 主键字段名
    @PageSize     int = 10,           -- 页尺寸
    @PageIndex    int = 1,            -- 页码
    @IsReCount    bit = 0,            -- 返回记录总数, 非 0 值则返回
    @OrderType    bit = 0,            -- 设置排序类型, 非 0 值则降序
    @OrderStr     varchar(255), 
    @strWhere     varchar(8000) = '' -- 查询条件 (注意: 不要加 where)
*/
   
create PROCEDURE [dbo].[UP_GetRecordByPage]
@tblName varchar(1000), --表名,多红表是请使用 tA a inner join tB b On a.AID = b.AID
@fldName varchar(100),    --主键，可以带表头 a.AID
@PageSize int = 10,        --页大小
@PageIndex int = 1,    --开始页码
@IsReCount    bit = 0,  
@OrderType    bit = 0, 
@OrderStr varchar(200) = '', --排序字段
@strWhere     varchar(8000) = '' -- 查询条件 (注意: 不要加 where)
AS
--
--select * from GL_NEWS order by GN_UPDATE_DATE DESC
--exec Pg_Paging @Tables = 'tb_NewsInfo', @PK = 'News_ID', @Sort = 'News_ID DESC', @PageNumber = 2, @PageSize = 15,@Fields = '*', @Group = '', @isCount = 0
DECLARE @strFilter varchar(2000)
declare @sql varchar(8000)
IF @strWhere IS NOT NULL AND @strWhere != ''
BEGIN
 SET @strFilter = ' AND ' + @strWhere + ''
END
ELSE
BEGIN
 SET @strFilter = ''
END


BEGIN
 if @OrderStr = ''
  set @OrderStr = @fldName 
 if @OrderType=1
 begin
  set @OrderStr =@OrderStr+' desc ' 
  end
 else
 begin
  set @OrderStr =@OrderStr+' asc ' 
  end
 IF @PageIndex < 1
  SET @PageIndex = 1

 if @PageIndex = 1 --第一页提高性能
 begin 
  set @sql = 'select top ' + str(@PageSize) +' *  from ' + @tblName + ' WHERE 1=1 ' + @strFilter + ' ORDER BY  '+ @OrderStr
 end 
 else
 begin
  /**//*Execute dynamic query*/    
  DECLARE @START_ID varchar(50)
  DECLARE @END_ID varchar(50)
  SET @START_ID = convert(varchar(50),(@PageIndex - 1) * @PageSize + 1)
  SET @END_ID = convert(varchar(50),@PageIndex * @PageSize)

  set @sql = 'with PageTable as(select ROW_NUMBER() OVER(ORDER BY '+@OrderStr+') rownum,* from '+@tblName+' WHERE 1=1 ' + @strFilter +' ) 
  SELECT * FROM PageTable 
  WHERE PageTable.rownum BETWEEN '+@START_ID+' AND '+@END_ID
 end
 
 IF @IsReCount = 1 --只获得记录条数
    begin
        set @sql = @sql+ 'SELECT  Count(*) FROM ' + @tblName + ' WHERE 1=1 ' + @strFilter  
    end
END
--print @sql

exec(@sql)

 

 调用存储过程:

  

--exec Pg_Paging 'DFWWMember','MemberAccount','15','3',1,1,'MemberAccount',''
--exec Pg_Paging @Tables = 'DFWWMember', @PK = 'MemberAccount', @Sort = 'MemberCreateDate', @PageNumber = 2, @PageSize = 15,@Fields = '*', @Group = '', @isCount = 1