教你快速掌握两个分页存储过程的用法

Create PROCEDURE [dbo].[Basic_Pagination2000]
@tblName      varchar(255),   -- 表名
@fidlelist    varchar(2000),  --要查询字段
@fldName      varchar(255),   -- 排序字段
@PageSize     int,            -- 页尺寸
@PageIndex    int,            -- 页码
@IsReCount    bit,            -- 返回记录总数, 非 0 值则返回
@OrderType    bit,            -- 设置排序类型, 非 0 值则降序
@strWhere     varchar(1000)   -- 查询条件 (注重: 不要加 where)
AS
declare @strSQL   varchar(6000)       -- 主语句
declare @strTmp   varchar(100),@tmpwhere  varchar(200)        -- 临时变量
declare @strOrder varchar(400)        -- 排序类型

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
set @tmpwhere='';
if(@strWhere!='')
begin
set @tmpwhere=' where ' @strWhere;
end
if @PageIndex = 1
begin
set @strSQL = 'select top ' 
  str(@PageSize)  ' ' @fidlelist ' ' 'from ['
  @tblName   '] '   @tmpwhere   ' '   @strOrder
end
else
begin
set @strSQL = 'select top ' 
  str(@PageSize)   ' ' @fidlelist ' ' 'from ['
  @tblName   '] where 
['   @fldName   ']'   @strTmp   '(['
  @fldName   ']) from 
(select top '   str((@PageIndex-1)*@PageSize)   ' ['
  @fldName   '] from 
['   @tblName   '] '   @tmpwhere   ' '
  @strOrder   ') as tblTmp)  
'   @tmpwhere   ' '   @strOrder
end
exec(@strSQL)
if @IsReCount != 0
begin
set @strSQL = 'select count(*) 
as Total from ['   @tblName   ']'  @strWhere
exec (@strSQL)
end

ALTER PROCEDURE [dbo].[Basic_Pagination2005]
@tblName      nvarchar(200),     --表名
@fidlelist    nvarchar(1000),   --要查询字段
@fldName      nvarchar(100),    --排序字段
@PageSize     int,              --页尺寸
@PageIndex    int,              --页码
@IsReCount    bit ,             -- 返回记录总数, 非 0 值则返回
@OrderType    bit,              -- 设置排序类型, 非 0 值则降序
@strWhere nvarchar(1000)        --查询条件
AS
declare @sqlstr nvarchar(4000),
@tmpwhere nvarchar(4000),@tmporder nvarchar(100)
BEGIN
if @OrderType != 0
begin
set @tmporder = @fldName  ' desc '
end
else
begin
        set @tmporder = @fldName  ' asc '
end
set @tmpwhere='';
if(@strWhere!='')
begin
set @tmpwhere=' where ' @strWhere;
end
set @sqlstr=N'select * from
(select  ' @fidlelist ', ROW_NUMBER() OVER(order

by ' @tmporder ') as row from ' @tblName @tmpwhere ') 
tmp where row between ' cast

(((@PageIndex-1)*@PageSize 1) as nvarchar) ' and ' cast

(@PageIndex*@PageSize as nvarchar);  
exec sp_executesql @sqlstr
if @IsReCount != 0
begin
set @sqlstr=N'select count(*) as Total from '  @tblName @tmpwhere
exec sp_executesql @sqlstr    
end
END