???????????????????????????????????????
???????д???洢????????
???????д?????????????????洢????????????д???????????????д????
???????????????????????д????
??????????д?????????????????????С??
??????????????????????????????к????С?????????????????????????????????????????????????????????ò????С??????????
??????????????1????????????￡?????д??д????洢???????????SQLSERVER2008????????????????2000????汾??????????ɡ?
????????????????????
if (object_id('Proc_Paging'?? 'P') is not null)
drop proc Proc_Paging
go
create procedure Proc_Paging
(
@TableName varchar(20)??--????
@PageIndex int=0??--????????????0???
@PageSize varchar(1000)='10'??--????????????????????????10?????
@Params varchar(100)='*'??--???????????????Σ???????????????
@ID varchar(20)='ID'??--????????????Σ?????ID
@OrderByID varchar(20)='asc'??--?????????????????????????
@StrWhere varchar(100)??--??????????????????????where???
@StrWhere2 varchar(100)??--???????????????????????????where???
@OrderBy varchar(100)='ID asc'--?????????
)
as
declare @Sql varchar(8000)??@PageStartNum varchar(1000)??@PageEndNum varchar(1000)??@Sql2 varchar(1000)??@Sql3 varchar(1000)
--1.???????????
set @PageStartNum=(@PageIndex)*(@PageSize)--?????????
set @PageEndNum=(@PageIndex+1)*(@PageSize)--?????????
set @Sql='select row_number() over(order by '+@ID+' '+@OrderByID+') Num??'+@Params+' '+
'into #temp '+
'from '+@TableName+' '+@StrWhere+''+
'select * from #temp where Num between '+
''+(@PageStartNum)+' and '+(@PageEndNum)+' '+@StrWhere2+' order by '+@OrderBy+' '
exec(@Sql)
--print @Sql
--2.?????????
set @Sql2='select count(1) from '+@TableName+''
exec(@Sql2)
--print @Sql2
--3.??????????
set @Sql3='select ceiling(count(1)*1.0/('+@PageSize+')) from '+@TableName+''
exec(@Sql3)
--print @Sql3
go
--???????
exec Proc_Paging 'Customers'??2??10??'CustomerID??CompanyName??Address??City??PostalCode'??'CustomerID'??'asc'??'where PostalCode>''1000'' '??'and Num>1 '??'Num asc'
go
?????????????н???????
??????н????