分页的存储过程

分页的存储过程

代码如下:
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