EXEC UP_SplitPages 'select RID,user_id,user_name,balance,user_state,
CONVERT(varchar(30),reg_time,11)+'' ''+CONVERT(varchar(30),reg_time,8) as reg_time,CONVERT(varchar(30),paid_time,11)+'' ''+CONVERT(varchar(30),paid_time,8) as paid_time,pay_type,user_grade,num_type,num_grade,numrent_type,agent_no,CONVERT(varchar(30),pause_time,11)+'' ''+CONVERT(varchar(30),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
作者“技术成就梦想”