分页的存储过程

复制代码代码如下:
Create procedure sp_pageQuery

@sqlstr nvarchar（4000）,
@page_index int,
@page_size int ,
@rec_count int out --
as
set nocount on
declare @cursor_id int
declare @rowcount int

exec sp_cursoropen @cursor_id output,@sqlstr,@scrollopt=1,@ccopt=1,@rowcount=@rowcount output

set @rec_count=@rowcount

set @page_index=（@page_index-1）*@page_size+1

IF @rec_count>0
  BEGIN
  exec sp_cursorfetch @cursor_id,16,@page_index,@page_size
  END
ELSE
  BEGIN
  Select "test"="null" Where 1=2
  END

exec sp_cursorclose @cursor_id
set nocount off
GO

在要用的时候在那个存储过程里调用
复制代码代码如下:
Create PROCEDURE [dev].[P_Mobile_Comment_Page]
@course_ware_id int,
@recCountPerPage int=1,
@pageIndex int =1,
@recordCount int=0 out
AS

DECLARE @sql nvarchar（4000）

SET @sql="
Select seg_id,course_ware_id,subject,cust_name,content,create_date
FROM T_COURSEWARE_COMMENT
Where course_ware_id="+cast（@course_ware_id as varchar（10））+"
ORDER BY seg_id"
EXEC sp_Pagequery @sql,@pageIndex,@recCountPerPage,@recordCount out

GO