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

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)ASdeclare @strSQL   varchar(6000)       -- 主语句declare @strTmp   varchar(100),@tmpwhere  varchar(200)        -- 临时变量declare @strOrder varchar(400)        -- 排序类型if @OrderType != 0beginset @strTmp = '<(select min'set @strOrder = ' order by ['   @fldName  '] desc'endelsebeginset @strTmp = '>(select max'set @strOrder = ' order by ['   @fldName  '] asc'endset @tmpwhere='';if(@strWhere!='')beginset @tmpwhere=' where ' @strWhere;endif @PageIndex = 1beginset @strSQL = 'select top '   str(@PageSize)  ' ' @fidlelist ' ' 'from ['  @tblName   '] '   @tmpwhere   ' '   @strOrderendelsebeginset @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   ' '   @strOrderendexec(@strSQL)if @IsReCount != 0beginset @strSQL = 'select count(*) as Total from ['   @tblName   ']'  @strWhereexec (@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)        --查询条件ASdeclare @sqlstr nvarchar(4000),@tmpwhere nvarchar(4000),@tmporder nvarchar(100)BEGINif @OrderType != 0beginset @tmporder = @fldName  ' desc 'endelsebegin        set @tmporder = @fldName  ' asc 'endset @tmpwhere='';if(@strWhere!='')beginset @tmpwhere=' where ' @strWhere;endset @sqlstr=N'select * from(select  ' @fidlelist ', ROW_NUMBER() OVER(orderby ' @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 @sqlstrif @IsReCount != 0beginset @sqlstr=N'select count(*) as Total from '  @tblName @tmpwhereexec sp_executesql @sqlstr    endEND