由于现在大部分 分页存储过程都支持有个排序字段。所以如果要是多字段排序分页的话就没法用了。所以自己写了个很实用的,但是估计效率不是很高,尤其是分页那部分
注意:由于时间问题,已经把表写死了。排序条件也写死了。如果有需要的话可以提成变量(表要动态提字段)
- create proc BListPage
@Filter varchar(500),
@indexPage int,
@PageSize int
as - declare @strsql varchar(8000)
declare @strStartRow varchar(100)
declare @strendRow varchar(100) - /*设置搜索条件*/
if(@Filter <> '')
Set @strsql = ' where ' + @Filter - /*起始行号*/
SET @strStartRow = CAST(((@indexPage - 1)*@PageSize+1) AS varchar(100)) - /*结束行号*/
SET @strendRow = CAST((@indexPage *@PageSize ) AS varchar(100)) - exec('
declare @tempTable table(tid int identity(1,1) ,
id int ,
Title varchar(1000) ,
SecondTitle varchar(1000) ,
Url varchar(1000) ,
ContentHtml ntext ,
Click int ,
isExpire int,
CountryID int ,
LastDate datetime,
SignName varchar(50) ,
SignDate datetime ,
LastUpdater varchar (50),
LastUpdate datetime,
GroupType char(10),
SchoolName varchar(50) ,
MeetingID int ,
AllMeeting varchar(100) - )
- insert @tempTable(
id,
Title,
SecondTitle,
Url,
ContentHtml,
Click,
isExpire,
CountryID,
LastDate,
SignName,
SignDate,
LastUpdater,
LastUpdate,
GroupType,
SchoolName,
MeetingID,
AllMeeting - ) select * from web_Bulletin '
+ @strsql +
' order by lastdate asc - select * from @tempTable where tid >= '+@strStartRow+' and tid <= '+@strendRow+' ')
- GO
- drop proc BListPage
- BListPage '',2,10