读取表内容 分页返回其读取的值

 

 

CREATE PROCEDURE DS_GetDataList
 (
  @tblName varchar(255) = '', -- 表名
  @strGetFields varchar(1000) = '*', -- 需要返回的列
  @fldName varchar(255) = 'ReleaseDate', -- 排序的字段名
  @PageSize int = 10, -- 页尺寸
  @PageIndex int = 1, -- 页码
  @RecordCount int output, --输出记录总数
  @OrderType bit = 1, -- 设置排序类型, 非 0 值则降序
  @strWhere varchar(1500) = '' -- 查询条件 (注意: 不要加 where)
 )
AS
 declare @strSQL varchar(5000) -- 主语句
 declare @CountSQL nvarchar(4000) -- 语句
 declare @strTmp varchar(1000) -- 临时变量
 declare @strOrder varchar(1000) -- 排序类型

 if @strWhere !=''
  set @CountSQL='select @RecordCount=count(*) from  [' + @tblName + '] where ' + @strWhere +''
 else
  set @CountSQL='select @RecordCount=count(*) from  [' + @tblName + '] '
 
 exec sp_executesql @CountSQL, N'@RecordCount int out ',@RecordCount out

 if @OrderType != 0
  begin
   set @strTmp = '<(select min'
   set @strOrder = ' order by '+ @fldName +' desc'
  end
 else
  begin
   set @strTmp = '>(select max'
   set @strOrder = ' order by '+ @fldName +' asc'
  end
 
 if @PageIndex = 1
  begin
   if @strWhere != ''
    set @strSQL = 'select top '+ str(@PageSize) +' '+ @strGetFields +' from ['+ @tblName +'] where ('+ @strWhere +') '+ @strOrder
   else
    set @strSQL = 'select top '+ str(@PageSize) +' '+ @strGetFields +' from ['+ @tblName +'] '+ @strOrder
  end
 else
  begin
   set @strSQL = 'select top '+ str(@PageSize) +' '+ @strGetFields +' from ['+ @tblName +'] where '+ @fldName +''+ @strTmp +'('+ @fldName +') from (select top '+ str((@PageIndex-1)*@PageSize) +' '+ @fldName +' from ['+ @tblName +']'+ @strOrder +') as tblTmp)'+ @strOrder

   if @strWhere != ''
    set @strSQL = 'select top '+ str(@PageSize) +' '+ @strGetFields +' from ['+ @tblName +'] where '+ @fldName +''+ @strTmp +'('+ @fldName +') from (select top '+ str((@PageIndex-1)*@PageSize) +' '+ @fldName +' from ['+ @tblName +'] where ('+ @strWhere +') '+ @strOrder +') as tblTmp) and '+ @strWhere +' '+ @strOrder
  end

 exec (@strSQL)

GO