sqlserver利用sp_cursoropen存储过程分页示例

declare @P1 int,@currIndex int;
declare @sql varchar(4000),@sql1 varchar(2000),@sql2 varchar(2000);
declare @data table(record_id int);

set @sql1 = 'select record_id from demo as t1 where 1=1 and in_qty >0';
set @sql2 = '';
set @currIndex=(@pageIndex-1)*@pagesize+1;

if @warehouseId > 0 set @sql2 = ' and t1.warehouse_id='+cast(@warehouseId as nvarchar);
if len(@startDate) > 0 set @sql2= @sql2 + ' and t1.bill_date >=''' + @startDate + '''';

set @sql = @sql1 + @sql2;
insert into @data exec  sp_cursoropen @P1 output,@sql,@scrollopt=1,@ccopt=1,@rowcount=@rowCount output;
insert into @data exec sp_cursorfetch @P1,16,@currIndex,@pagesize;
exec sp_cursorclose @P1;