分页的存储过程
程序员文章站
2022-04-16 09:52:29
复制代码 代码如下:create procedure sp_pagequery @sqlstr nvarchar(4000), @...
复制代码 代码如下:
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