sqlserver分页

?

 
 

EXEC?UP_SplitPages?'select?RID,user_id,user_name,balance,user_state,
  
  
CONVERT(varchar(30),reg_time,11)+''?''+CONVERT(varchar(30),8)?as?reg_time,CONVERT(varchar(30),paid_time,8)?as?paid_time,pay_type,user_grade,num_type,num_grade,numrent_type,agent_no,pause_time,8)?as?pause_time?from?VIEW_UserInfo?with?(nolock)?where?1=1','reg_time?DESC',1?,100,?0??
  
  
?
  
  
?
  
  
?
  
  
?
  
  
?
  
  
?
  
  
?
  
  
CREATE??PROCEDURE?UP_SplitPages?
  
  
????@SqlQuery?NVARCHAR(4000),?--查询字符串?
  
  
????@OrderFieldName??VARCHAR(100),??--按该列为关键字来进行排序分页?
  
  
????@CurrentPage?INT,--第N页?（如果是?0??则读取?最后?一页的记录??）?
  
  
????@PageSize?INT,--每页行数?
  
  
????@OrderType???INT??????
  
  
?
  
  
AS?
  
  
?
  
  
DECLARE?@sql?NVARCHAR(4000)?
  
  
DECLARE?@tPageCount?INT?
  
  
?
  
  
IF?len(@OrderFieldName)>3?
  
  
????SET?@sql=?@SqlQuery?+?'??ORDER?BY??'+@OrderFieldName?
  
  
ELSE?
  
  
?????????????SET?@sql=?@SqlQuery?
  
  
?????
  
  
?
  
  
BEGIN?
  
  
????SET?NOCOUNT?ON?
  
  
????
  
  
????DECLARE?@P1?INT--P1是游标的id?
  
  
????DECLARE?@rowcount?INT?
  
  
?
  
  
????????????
  
  
?
  
  
?????
  
  
????EXEC?sp_cursoropen?@P1?OUTPUT,@sql,@scrollopt=1,@ccopt=1,@rowcount=@rowcount?OUTPUT?
  
  
?
  
  
????????????SELECT??@rowcount?AS?RowsCount,@CurrentPage?AS?CurrentPage???
  
  
?
  
  
????SET??@tPageCount?=?CEILING(1.0*@rowcount/@PageSize)?
  
  
?
  
  
????IF?@CurrentPage?=?0??
  
  
????????SET??@CurrentPage?=?@tPageCount--?@PageIndex?=?0?表示在调用时，是首次查询，先读取?最后?一页的记录???
  
  
?
  
  
????SET?@CurrentPage=(@CurrentPage-1)*@PageSize+1?
  
  
?
  
  
?
  
  
????EXEC?sp_cursorfetch?@P1,16,@CurrentPage,@PageSize??
  
  
????EXEC?sp_cursorclose?@P1?
  
  
?
  
  
????--SELECT??@tPageCount?AS?PagesCount?,@rowcount?AS?RowsCount,@CurrentPage?AS?CurrentPage???
  
  
????????SET?NOCOUNT?OFF?
  
  
END?
  
  
?
  
  
GO?
 
 

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!