一个分页存储过程的学习

Create procedure jxytext_pagination @sqlstr nvarchar(4000), --查询字符串 @currentpage int, --第N页 @pagesize int --每页行数 as set nocount on declare @P1 int, --P1是游标的id @rowcount int exec sp_cursoropen @P1 output,@sqlstr,@scrollopt=1,@ccopt=1,@rowcount=@rowcount output select ceiling(1.0*@rowcount/@pagesize) as TotalPages,@rowcount as TotalRows,@currentpage as CurPage set @currentpage=(@currentpage-1)*@pagesize+1 exec sp_cursorfetch @P1,16,@currentpage,@pagesize exec sp_cursorclose @P1 set nocount off GO

demo:

declare @sql varchar(1000) set @sql='select * from cxloanbal' exec jxytext_pagination @sql,1,10

其中 @sql 是你想执行的sql语句，那个1是 从起始记录的index，那个10是 你想每页显示的记录数

返回三个结果集 第一个是空的 第二个返回的分别是 总页数，总行数，起始记录的index，第三个结果集是 你想要的结果集

.多个数据集的提取和应用主要是Dr.NextResult();

using System; using System.Data; using System.Configuration; using System.Collections; using System.Web; using System.Web.Security; using System.Web.UI; using System.Web.UI.WebControls; using System.Web.UI.WebControls.WebParts; using System.Web.UI.HtmlControls; using DAL; using System.Data.SqlClient; public partial class Default6 : System.Web.UI.Page { protected void Page_Load(object sender, EventArgs e) { SqlDataReader Dr ; Dr = SqlHelper.ExecuteReader(SqlHelper.CONNECTION_STRING, CommandType.StoredProcedure, "Pr_jxytest2"); Dr.NextResult(); while (Dr.Read()) { Response.Write(Dr["Totalpages"]+"--"+Dr["Totalrows"]+"--"+Dr["Curpage"]); } Dr.NextResult(); while (Dr.Read()) { Response.Write(Dr["roomname"]+"<br>"); } } }