//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
然后再程序里调用存储过程