alter procedure qry_page
@sqlstr nvarchar(4000), --查询字符串
@page int, --第N页
@pagesize int --每页行数
as
declare @rscounts int --记录总数
declare @pages int --页面总数
set nocount on
declare @P1 int --P1是游标的id
exec sp_cursoropen @P1 output,@sqlstr,@scrollopt=1,@ccopt=1,@RecordCount=@rscounts output
set @pages = ceiling(1.0*@rscounts/@pagesize)
if @page<=0 set @page=1
if @page>@pages set @page=@pages
--当前页面条数，不足一页
declare @curpg_counts int
if @rscounts<@page*@pagesize
set @curpg_counts=@rscounts-(@page-1)*@pagesize
else
set @curpg_counts=@pagesize
--当前页起始行,从1开始
declare @startPos int
set @startPos=(@page-1)*@pagesize+1
--获取指定记录
exec sp_cursorfetch @P1,16,@startPos,@curpg_counts
exec sp_cursorclose @P1
set nocount off
--另外返回记录总数/总页面数/当前页/每页条数
select rscounts=@rscounts,pages=@pages,pagesize=@pagesize,page=@page
--exec qry_page 'select * from user_record_in order by id',3,5
客户端访问程序
delphi版本:
unit Unit1;
interface
uses
Windows, Messages, SysUtils, Variants, Classes, Graphics, Controls, Forms,
Dialogs, StdCtrls, Buttons, Grids, DBGrids, ExtCtrls, DB, ADODB, Mask;
type
TForm1 = class(TForm)
Panel1: TPanel;
DBGrid1: TDBGrid;
SpeedButton6: TSpeedButton;
SpeedButton7: TSpeedButton;
Label5: TLabel;
SpeedButton1: TSpeedButt
