//from:http://www.ohuo.net/?p=84
if exists(select * from sysobjects where name=’Proc_MyPagination’) drop procedure Proc_MyPagination go
create procedure Proc_MyPagination /* *************************************************************** ** 分页存储过程 ** *************************************************************** 参数说明: 1.Tables :表名称,视图 2.PrimaryKey :主关键字 3.Sort :排序语句,不带Order By 比如:NewsID Desc,OrderRows Asc 4.CurrentPage :当前页码 5.PageSize :分页尺寸 6.Fields :字段 7.Filter :过滤语句,不带Where 8.Group :Group语句,不带Group By 9.GetCount :返回记录总数, 非 0 值则返回 ***************************************************************/ @Tables varchar(600), @PrimaryKey varchar(100), @Sort varchar(200)=null, @CurrentPage bigint=1, @PageSize bigint=10, @Fields varchar(1000)=’*', @Filter varchar(1000)=null, @Group varchar(1000)=null, @GetCount bit=0 as if(@GetCount=0) begin
/*默认排序*/ if @Sort is null or @Sort=” set @Sort=@PrimaryKey declare @SortTable varchar(100) declare @SortName varchar(100) declare @strSortColumn varchar(200) declare @operator char(2) declare @type varchar(100) declare @prec int /*设定排序语句*/ if charindex(’desc’,@Sort)>0 begin set @strSortColumn=replace(@Sort,’desc’,”) set @operator=’<=’ end else begin if charindex(’asc’,@Sort)=0 set @strSortColumn=replace(@Sort,’asc’,”) set @operator=’>=’ end if charindex(’.',@strSortColumn)>0 begin set @SortTable=substring(@strSortColumn,0,charindex(’.',@strSortColumn)) set @SortName=substring(@strSortColumn,charindex(’.',@strSortColumn)+1,len(@strSortColumn)) end else begin set @SortTable=@Tables set @SortName=@strSortColumn end select @type=t.name,@prec=c.prec from sysobjects o join syscolumns c on o.id=c.id join systypes t on c.xusertype=t.xusertype where o.name=@SortTable and c.name=@SortName if charindex(’char’,@type)>0 set @type=@type+’('+cast(@prec as varchar)+’)’ declare @strPageSize varchar(50) declare @strStartRow varchar(50) declare @strFilter varchar(1000) declare @strSimpleFilter varchar(1000) declare @strGroup varchar(1000) /*默认当前页*/ if @CurrentPage<1 set @CurrentPage=1 /*设置分页参数*/ set @strPageSize=cast(@PageSize as varchar(50)) set @strStartRow=cast(((@CurrentPage-1)*@PageSize+1) as varchar(50)) /*筛选以及分组语句*/ if @Filter is not null and @Filter!=” begin set @strFilter=’ where ‘+@Filter+’ ‘ set @strSimpleFilter=’ and ‘+@Filter +’ ‘ end else begin set @strSimpleFilter=” set @strFilter=” end if @Group is not null and @Group!=” set @strGroup=’ group by ‘+@Group+’ ‘ else set @strGroup=” exec( ’ declare @SortColumn ‘+ @type + ‘ set RowCount ‘ + @strStartRow + ‘ select @SortColumn=’ + @strSortColumn + ‘ from ‘ + @Tables + @strFilter + ‘ ‘ + @strGroup + ‘ Order by ‘ + @Sort + ‘ set rowcount ‘ + @strPageSize + ‘ select ‘ + @Fields + ‘ from ‘ + @Tables + ‘ where ‘ + @strSortColumn + @operator + ‘@SortColumn ‘ + @strSimpleFilter + ‘ ‘ + @strGroup + ‘ Order by ‘ + @Sort + ‘ ‘) end else begin declare @strSQL varchar(5000) if @Filter !=” set @strSQL = ’select count(’ + @PrimaryKey + ‘) as Total from [’ + @Tables + ‘] where ‘ + @Filter else set @strSQL = ’select count(’ + @PrimaryKey + ‘) as Total from [’ + @Tables + ‘]’ exec(@strSQL) end go
然后再程序里调用存储过程