翻页存储过程 2007-3-5

/*
此存储过程的用法
BaseSelect 'select * from orders',3
3表示第3页
*/

SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

 

CREATE PROCEDURE BaseSelect
(
 @Sql   nvarchar(4000),
 @CurentPage  int =1 ,
 @PageSize  int =10
)
--WITH  ENCRYPTION
AS
 SET NOCOUNT ON
 Declare   
  @Pc    int,
  @PageCount  int,
  @RecordCount int
 
 Exec sp_cursoropen @pc output, @Sql, 1 ,1,@rowcount = @RecordCount output
  
 Select @PageCount = ceiling( 1.0 * @RecordCount / @PageSize ),
   @CurentPage = ( @CurentPage - 1 ) * @PageSize +1
 
 Exec sp_cursorfetch @Pc, 16, @CurentPage, @PageSize
 
 Exec sp_cursorclose @Pc
 
 select @PageCount as PageCount, @RecordCount as RecordCount
 
 RETURN
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO