CREATE PROCEDURE PageList @tablename varchar(100), @fields varchar(800),@condition varchar(8000), @orderfield varchar(100),@ordertype varchar(100), @pagesize int,@PageNo int AS declare @SQL varchar(8000)if (@ordertype = 'Desc') begin if (@condition = 'None') begin if (@PageNo = 1) begin set @SQL= 'Select Top '+cast(@pagesize as varchar(50)) +' '+ @fields +' From '+ @tablename +' Order By '+ @orderfield +' Desc' end if (@PageNo > 1) begin set @SQL= 'Select Top '+cast(@pagesize as varchar(50)) +' '+ @fields +' From '+ @tablename +' Where '+ @orderfield +'<(Select Min('+ @orderfield +') From (Select Top '+ cast(@pagesize * (@PageNo - 1) as varchar(50)) +' '+ @orderfield +' From '+ @tablename +' Order By '+ @orderfield +' Desc) Temp) Order By '+ @orderfield +' Desc' end end else begin if (@PageNo = 1) begin set @SQL= 'Select Top '+cast(@pagesize as varchar(50)) +' '+ @fields +' From '+ @tablename +' '+ @condition +' Order By '+ @orderfield +' Desc' end if (@PageNo > 1) begin set @SQL= 'Select Top '+cast(@pagesize as varchar(50)) +' '+ @fields +' From '+ @tablename +' '+ @condition +' And '+ @orderfield +'<(Select Min('+ @orderfield +') From (Select Top '+ cast(@pagesize * (@PageNo - 1) as varchar(50)) +' '+ @orderfield +' From '+ @tablename +' '+ @condition +' Order By '+ @orderfield +' Desc) Temp) Order By '+ @orderfield +' Desc' end end end else if (@condition = 'None') begin if (@PageNo = 1) begin set @SQL= 'Select Top '+cast(@pagesize as varchar(50)) +' '+ @fields +' From '+ @tablename +' Order By '+ @orderfield +' Asc' end if (@PageNo > 1) begin set @SQL= 'Select Top '+cast(@pagesize as varchar(50)) +' '+ @fields +' From '+ @tablename +' Where '+ @orderfield +'>(Select Max('+ @orderfield +') From (Select Top '+ cast(@pagesize * (@PageNo - 1) as varchar(50)) +' '+ @orderfield +' From '+ @tablename +' Order By '+ @orderfield +' Asc) Temp) Order By '+ @orderfield +' Asc' end end else begin if (@PageNo = 1) begin set @SQL= 'Select Top '+cast(@pagesize as varchar(50)) +' '+ @fields +' From '+ @tablename +' '+ @condition +' Order By '+ @orderfield +' Asc' end if (@PageNo > 1) begin set @SQL= 'Select Top '+cast(@pagesize as varchar(50)) +' '+ @fields +' From '+ @tablename +' '+ @condition +' And '+ @orderfield +'>(Select Max('+ @orderfield +') From (Select Top '+ cast(@pagesize * (@PageNo - 1) as varchar(50)) +' '+ @orderfield+' From '+ @tablename +' '+ @condition +' Order By '+ @orderfield +' Asc) Temp) Order By '+ @orderfield +' Asc' end end exec (@SQL) GO
CREATE PROCEDURE PageList1 ( @TableName varchar(100),@Fields varchar(8000), @Condition varchar(8000),@OrderField varchar(50), @OrderType varchar(4),@PageSize int,@PageNo int ) AS --临时查询语句 declare @TempSql varchar(8000) declare @PageLowerBound int, @PageUpperBound int set @PageLowerBound=(@PageNo-1)*@PageSize set @PageUpperBound=@PageLowerBound+@PageSize --当记录数达到@PageUpperBound时就会停止处理查询 set rowcount @PageUpperBound set nocount on if (@PageNo<=1) begin if (@Condition='' or @Condition='None') begin set @TempSql='select top '+cast(@PageSize as varchar)+' '+ @Fields +' from '+@TableName +' Order By '+ @OrderField +' '+@OrderType end else begin set @TempSql='select top '+cast(@PageSize as varchar)+' '+ @Fields +' from '+@TableName +' '+@Condition+' Order By '+ @OrderField +' '+@OrderType end exec(@TempSql)end else begin --建立临时表保存要查询的表(视图)的标识字段 CREATE table #indextable (TempID int PRIMARY key identity(1,1) not null,xValues int) --只把id列取出放到临时表里 if (@Condition='' or @Condition='None') begin set @TempSql='insert into #indextable(xValues) select '+@OrderField+' from '+@TableName+' Order By '+ @OrderField +' '+@OrderType end else begin set @TempSql='insert into #indextable(xValues) select '+@OrderField+' from '+@TableName+' '+@Condition+' Order By '+ @OrderField +' '+@OrderType end exec(@TempSql) --只从表中取出所需要的记录,而不是所有的记录 set @TempSql='select '+@Fields+' from '+@TableName+',#indextable t where '+@OrderField+'=t.xValues and t.TempID>'+cast(@PageLowerBound as varchar)+' and t.TempID<='+cast(@PageUpperBound as varchar)+' order by t.TempID' exec(@TempSql) --print @TempSql end set nocount off GO
------------------------------------ --用途:分页存储过程(对有主键的表效率极高) --说明: ------------------------------------CREATE PROCEDURE UP_GetRecordByPage @tblName varchar(255), -- 表名 @fldName varchar(255), -- 主键字段名 @PageSize int = 10, -- 页尺寸 @PageIndex int = 1, -- 页码 @IsReCount bit = 0, -- 返回记录总数, 非 0 值则返回 @OrderType bit = 0, -- 设置排序类型, 非 0 值则降序 @strWhere varchar(1000) = '' -- 查询条件 (注意: 不要加 where) ASdeclare @strSQL varchar(6000) -- 主语句 declare @strTmp varchar(100) -- 临时变量 declare @strOrder varchar(400) -- 排序类型if @OrderType != 0 begin set @strTmp = '<(select min' set @strOrder = ' order by [' + @fldName +'] desc' end else begin set @strTmp = '>(select max' set @strOrder = ' order by [' + @fldName +'] asc' endset @strSQL = 'select top ' + str(@PageSize) + ' * from [' + @tblName + '] where [' + @fldName + ']' + @strTmp + '([' + @fldName + ']) from (select top ' + str((@PageIndex-1)*@PageSize) + ' [' + @fldName + '] from [' + @tblName + ']' + @strOrder + ') as tblTmp)' + @strOrderif @strWhere != '' set @strSQL = 'select top ' + str(@PageSize) + ' * from [' + @tblName + '] where [' + @fldName + ']' + @strTmp + '([' + @fldName + ']) from (select top ' + str((@PageIndex-1)*@PageSize) + ' [' + @fldName + '] from [' + @tblName + '] where ' + @strWhere + ' ' + @strOrder + ') as tblTmp) and ' + @strWhere + ' ' + @strOrderif @PageIndex = 1 begin set @strTmp ='' if @strWhere != '' set @strTmp = ' where ' + @strWhere set @strSQL = 'select top ' + str(@PageSize) + ' * from [' + @tblName + ']' + @strTmp + ' ' + @strOrder endif @IsReCount != 0 set @strSQL = 'select count(*) as Total from [' + @tblName + ']'+' where ' + @strWhereexec (@strSQL)GO
SQL2005 create proc mxw_proc @pagesize int as begin begin tran declare @sqlstr nvarchar(500) set @sqlstr='select top 10* from(select id ,email,ROW_NUMBER() OVER (ORDER BY Id) AS RowNo FROM MemberReg )as a Where RowNo >'+cast(10*(@pagesize) as varchar) exec(@sqlstr) if(@@error!=0) begin raiserror 20000 'mxw_proc error' rollback tran return (1) end commit tran end
如:已查出总条数为X条; 每页20条; 并已计算出有Y页;(确定排序方式为ID ASC); 要取第Z页(不是最后一页)的数据 则查询语句为"select * From (select Top 20 * From (select Top "+(20*Z).ToString()+" * From Table Order by ID ASC) Order by ID DESC) Order by ID Asc" 最外面一个select只为重新排序,中间两个查询是实际内容。 当Z=Y时要单独写。 "select Top 20 * From (select Top " + (X-(Y-1)*20).ToString()+ " FromTable Order by ID DESC) Order by ID ASC //附:得到X的查询方法 select Count(*) From Table//详情请查询:http://www.gisshop.cn/blog/Blog.aspx?ID=4 数据查出来了,显示就应该很容易了吧...
分页存储过程
可以看看我blog
{
if(Request.QueryString["Page"]!=null)
{
PageP=Request.QueryString["Page"].ToString();
}
lb_url.Text = url;
if (Request.QueryString["nodeid"]!=null)
{
Params = "&nodeid="+Request.QueryString["nodeid"]+"";
}
lb_Params.Text = Params;
//创建分页类
PagedDataSource objPage = new PagedDataSource();
//设置数据源
objPage.DataSource = dt.DefaultView;
//允许分页
objPage.AllowPaging = true;
//设置每页显示的项数
objPage.PageSize = pagesize;
//设置当前页的索引
int CurPage=1;
try
{
CurPage = Convert.ToInt32(PageP);
if (CurPage<1 && CurPage>objPage.PageCount)
{
Response.Redirect(url+"?page=1"+Params);
}
if (CurPage>1 && CurPage>objPage.PageCount)
{
Response.Redirect(url+"?page=1"+Params);
}
}
catch
{
Response.Redirect(url+"?page=1"+Params);
}
objPage.CurrentPageIndex = CurPage-1;
//显示状态信息
lb_ItemCount.Text = dt.Rows.Count.ToString();
lb_CurrentPage.Text = CurPage.ToString();
lb_PageCount.Text =objPage.PageCount.ToString();
//如果当前页面不是首页
if (!objPage.IsFirstPage)
{
hpl_Prev.NavigateUrl=url + "?Page=" + Convert.ToString(CurPage-1)+Params;
hpl_First.NavigateUrl=url + "?Page=1"+Params;
}
//如果当前页面不是最后一页
if (!objPage.IsLastPage)
{
hpl_Next.NavigateUrl=url+ "?Page=" + Convert.ToString(CurPage+1)+Params;
hpl_Last.NavigateUrl=url + "?Page=" +objPage.PageCount.ToString()+Params;
}
return objPage;
}调用方法dlistCaseList.DataSource = databind(ds.Tables[0]);
dlistCaseList.DataBind();
整个CS文件
namespace web.usercontrol.product
{
using System;
using System.Data;
using System.Drawing;
using System.Web;
using System.Web.UI.WebControls;
using System.Web.UI.HtmlControls; /// <summary>
/// winerCase 的摘要说明。
/// </summary>
public class winerCase : System.Web.UI.UserControl
{
protected Hooyee.WebControls.AdvDataList dlistCaseList;
public int pagesize=10;
public string PageP="";
public string Params;
public string url="/web/SucceedCase_Index.aspx";
protected System.Web.UI.WebControls.Label lb_ItemCount;
protected System.Web.UI.WebControls.HyperLink hpl_First;
protected System.Web.UI.WebControls.HyperLink hpl_Prev;
protected System.Web.UI.WebControls.Label lb_CurrentPage;
protected System.Web.UI.WebControls.Label lb_PageCount;
protected System.Web.UI.WebControls.HyperLink hpl_Next;
protected System.Web.UI.WebControls.HyperLink hpl_Last;
protected System.Web.UI.WebControls.TextBox txb_Page;
protected System.Web.UI.WebControls.ImageButton btn_go;
protected System.Web.UI.WebControls.Label lb_url;
protected System.Web.UI.WebControls.Label lb_Params; CommonLogic.admin.adminSuccedCase succeedCaseBLL = new CommonLogic.admin.adminSuccedCase(); private void Page_Load(object sender, System.EventArgs e)
{
databind();
} // 根据不同的会员,显示不同的下载文件列表
void databind()
{
int BranchID=5;
if(Session["branch"]!=null)
{
BranchID = Convert.ToInt32( Session["branch"]);
}
DataSet ds = new DataSet();
string nodeid = Request.QueryString["nodeid"];
if (nodeid != null)
{
succeedCaseBLL.GetSucceedCases(BranchID,Convert.ToInt32(nodeid),out ds);
}
else
{
succeedCaseBLL.GetSucceedCases(BranchID,out ds);
}
dlistCaseList.DataSource = databind(ds.Tables[0]);
dlistCaseList.DataBind();
} public PagedDataSource databind(DataTable dt)
{
if(Request.QueryString["Page"]!=null)
{
PageP=Request.QueryString["Page"].ToString();
}
lb_url.Text = url;
if (Request.QueryString["nodeid"]!=null)
{
Params = "&nodeid="+Request.QueryString["nodeid"]+"";
}
lb_Params.Text = Params;
//创建分页类
PagedDataSource objPage = new PagedDataSource();
//设置数据源
objPage.DataSource = dt.DefaultView;
//允许分页
objPage.AllowPaging = true;
//设置每页显示的项数
objPage.PageSize = pagesize;
//设置当前页的索引
int CurPage=1;
try
{
CurPage = Convert.ToInt32(PageP);
if (CurPage<1 && CurPage>objPage.PageCount)
{
Response.Redirect(url+"?page=1"+Params);
}
if (CurPage>1 && CurPage>objPage.PageCount)
{
Response.Redirect(url+"?page=1"+Params);
}
}
catch
{
Response.Redirect(url+"?page=1"+Params);
}
objPage.CurrentPageIndex = CurPage-1;
//显示状态信息
lb_ItemCount.Text = dt.Rows.Count.ToString();
lb_CurrentPage.Text = CurPage.ToString();
lb_PageCount.Text =objPage.PageCount.ToString();
//如果当前页面不是首页
if (!objPage.IsFirstPage)
{
hpl_Prev.NavigateUrl=url + "?Page=" + Convert.ToString(CurPage-1)+Params;
hpl_First.NavigateUrl=url + "?Page=1"+Params;
}
//如果当前页面不是最后一页
if (!objPage.IsLastPage)
{
hpl_Next.NavigateUrl=url+ "?Page=" + Convert.ToString(CurPage+1)+Params;
hpl_Last.NavigateUrl=url + "?Page=" +objPage.PageCount.ToString()+Params;
}
return objPage;
} #region Web 窗体设计器生成的代码
override protected void OnInit(EventArgs e)
{
//
// CODEGEN: 该调用是 ASP.NET Web 窗体设计器所必需的。
//
InitializeComponent();
base.OnInit(e);
}
/// <summary>
/// 设计器支持所需的方法 - 不要使用代码编辑器
/// 修改此方法的内容。
/// </summary>
private void InitializeComponent()
{
this.Load += new System.EventHandler(this.Page_Load); }
#endregion private void dlistCaseList_AfterPageChanged(object source, System.Web.UI.WebControls.DataGridPageChangedEventArgs e)
{
databind();
} private void btn_go_Click(object sender, System.Web.UI.ImageClickEventArgs e)
{
Response.Redirect(lb_url.Text+"?Page="+txb_Page.Text+lb_Params.Text);
databind();
}
}
}
不过是gridview的,可以参考下 第<asp:Label ID="Label5" runat="server"></asp:Label>页
共<asp:Label ID="Label6" runat="server"></asp:Label>页
<asp:Label ID="Label7" runat="server"></asp:Label>条记录
<asp:linkbutton ID="Label8" OnClick="fn_page" CommandName="first" Font-Underline="false" ForeColor="#666666" runat="server" Text=" <b><<</b> " BorderColor="#666666" BackColor="#FFCD4A" BorderWidth="1"></asp:linkbutton>
<asp:linkbutton ID="Label9" OnClick="fn_page" CommandName="prev" Font-Underline="false" ForeColor="#666666" runat="server" Text=" <b><</b> " BorderColor="#666666" BackColor="#FFCD4A" BorderWidth="1"></asp:linkbutton>
<asp:linkbutton ID="Label10" OnClick="new_page" Font-Underline="false" ForeColor="#666666" runat="server" BorderColor="#666666" BackColor="#FFCD4A" BorderWidth="1"></asp:linkbutton>
<asp:linkbutton ID="Label11" OnClick="new_page" Font-Underline="false" ForeColor="#666666" runat="server" BorderColor="#666666" BackColor="#FFCD4A" BorderWidth="1"></asp:linkbutton>
<asp:linkbutton ID="Label12" OnClick="new_page" Font-Underline="false" ForeColor="#666666" runat="server" BorderColor="#666666" BackColor="#FFCD4A" BorderWidth="1"></asp:linkbutton>
<asp:linkbutton ID="Label13" OnClick="new_page" Font-Underline="false" ForeColor="#666666" runat="server" BorderColor="#666666" BackColor="#FFCD4A" BorderWidth="1"></asp:linkbutton>
<asp:linkbutton ID="Label14" OnClick="new_page" Font-Underline="false" ForeColor="#666666" runat="server" BorderColor="#666666" BackColor="#FFCD4A" BorderWidth="1"></asp:linkbutton>
<asp:linkbutton ID="Label15" OnClick="fn_page" CommandName="next" Font-Underline="false" ForeColor="#666666" runat="server" Text=" <b>></b> " BorderColor="#666666" BackColor="#FFCD4A" BorderWidth="1"></asp:linkbutton>
<asp:linkbutton ID="Label16" OnClick="fn_page" CommandName="last" Font-Underline="false" ForeColor="#666666" runat="server" Text=" <b>>></b> " BorderColor="#666666" BackColor="#FFCD4A" BorderWidth="1"></asp:linkbutton>
Sub new_page(ByVal s As System.Object, ByVal e As System.EventArgs)
Dim str As Int16 = Convert.ToInt16(CType(s, LinkButton).Text)
GridView1.PageIndex = str - 1
GridView1.EditIndex = -1
strsql = ViewState("strsql")
BindList()
End Sub Sub fn_page(ByVal s As System.Object, ByVal e As System.EventArgs)
Dim str As String = CType(s, LinkButton).CommandName.ToString
Select Case str
Case "first"
GridView1.PageIndex = 0
GridView1.EditIndex = -1
strsql = ViewState("strsql")
BindList()
Case "prev"
If GridView1.PageIndex > 0 Then
GridView1.PageIndex = GridView1.PageIndex - 1
Else
GridView1.PageIndex = 0
End If
GridView1.EditIndex = -1
strsql = ViewState("strsql")
BindList()
Case "next"
GridView1.PageIndex = GridView1.PageIndex + 1
GridView1.EditIndex = -1
strsql = ViewState("strsql")
BindList()
Case "last"
GridView1.PageIndex = GridView1.PageCount - 1
GridView1.EditIndex = -1
strsql = ViewState("strsql")
BindList()
End Select
End Sub Sub BindList()
Dim objcmd As New OleDbDataAdapter(strsql, StrCon)
Dim das As New DataSet
objcmd.Fill(das, "acount")
das.Tables("acount").DefaultView.Sort = ViewState("sort") & " " & ViewState("dire")
GridView1.DataSource = das.Tables("acount").DefaultView
GridView1.DataBind()
Label5.Text = GridView1.PageIndex + 1
Label6.Text = GridView1.PageCount
Label7.Text = das.Tables(0).Rows.Count
Label10.Text = Int((GridView1.PageIndex) / 5) * 5 + 1
Label11.Text = Int((GridView1.PageIndex) / 5) * 5 + 2
Label12.Text = Int((GridView1.PageIndex) / 5) * 5 + 3
Label13.Text = Int((GridView1.PageIndex) / 5) * 5 + 4
Label14.Text = Int((GridView1.PageIndex) / 5) * 5 + 5
If Label10.Text = GridView1.PageIndex + 1 Then
Label10.BackColor = Drawing.Color.White
Else
Label10.BackColor = Drawing.ColorTranslator.FromHtml("#FFCD4A")
End If
If Label11.Text = GridView1.PageIndex + 1 Then
Label11.BackColor = Drawing.Color.White
Else
Label11.BackColor = Drawing.ColorTranslator.FromHtml("#FFCD4A")
End If
If Label12.Text = GridView1.PageIndex + 1 Then
Label12.BackColor = Drawing.Color.White
Else
Label12.BackColor = Drawing.ColorTranslator.FromHtml("#FFCD4A")
End If
If Label13.Text = GridView1.PageIndex + 1 Then
Label13.BackColor = Drawing.Color.White
Else
Label13.BackColor = Drawing.ColorTranslator.FromHtml("#FFCD4A")
End If
If Label14.Text = GridView1.PageIndex + 1 Then
Label14.BackColor = Drawing.Color.White
Else
Label14.BackColor = Drawing.ColorTranslator.FromHtml("#FFCD4A")
End If
End Sub
http://community.csdn.net/Expert/topic/5290/5290374.xml?temp=.5702173
也许能帮你
@tablename varchar(100),
@fields varchar(800),@condition varchar(8000),
@orderfield varchar(100),@ordertype varchar(100),
@pagesize int,@PageNo int AS
declare @SQL varchar(8000)if (@ordertype = 'Desc')
begin
if (@condition = 'None')
begin
if (@PageNo = 1)
begin
set @SQL= 'Select Top '+cast(@pagesize as varchar(50)) +' '+ @fields +' From '+ @tablename +' Order By '+ @orderfield +' Desc'
end
if (@PageNo > 1)
begin
set @SQL= 'Select Top '+cast(@pagesize as varchar(50)) +' '+ @fields +' From '+ @tablename +' Where '+ @orderfield +'<(Select Min('+ @orderfield +') From (Select Top '+ cast(@pagesize * (@PageNo - 1) as varchar(50)) +' '+ @orderfield +' From '+ @tablename +' Order By '+ @orderfield +' Desc) Temp) Order By '+ @orderfield +' Desc'
end
end
else
begin
if (@PageNo = 1)
begin
set @SQL= 'Select Top '+cast(@pagesize as varchar(50)) +' '+ @fields +' From '+ @tablename +' '+ @condition +' Order By '+ @orderfield +' Desc'
end
if (@PageNo > 1)
begin
set @SQL= 'Select Top '+cast(@pagesize as varchar(50)) +' '+ @fields +' From '+ @tablename +' '+ @condition +' And '+ @orderfield +'<(Select Min('+ @orderfield +') From (Select Top '+ cast(@pagesize * (@PageNo - 1) as varchar(50)) +' '+ @orderfield +' From '+ @tablename +' '+ @condition +' Order By '+ @orderfield +' Desc) Temp) Order By '+ @orderfield +' Desc'
end
end
end
else if (@condition = 'None')
begin
if (@PageNo = 1)
begin
set @SQL= 'Select Top '+cast(@pagesize as varchar(50)) +' '+ @fields +' From '+ @tablename +' Order By '+ @orderfield +' Asc'
end
if (@PageNo > 1)
begin
set @SQL= 'Select Top '+cast(@pagesize as varchar(50)) +' '+ @fields +' From '+ @tablename +' Where '+ @orderfield +'>(Select Max('+ @orderfield +') From (Select Top '+ cast(@pagesize * (@PageNo - 1) as varchar(50)) +' '+ @orderfield +' From '+ @tablename +' Order By '+ @orderfield +' Asc) Temp) Order By '+ @orderfield +' Asc'
end
end
else
begin
if (@PageNo = 1)
begin
set @SQL= 'Select Top '+cast(@pagesize as varchar(50)) +' '+ @fields +' From '+ @tablename +' '+ @condition +' Order By '+ @orderfield +' Asc'
end
if (@PageNo > 1)
begin
set @SQL= 'Select Top '+cast(@pagesize as varchar(50)) +' '+ @fields +' From '+ @tablename +' '+ @condition +' And '+ @orderfield +'>(Select Max('+ @orderfield +') From (Select Top '+ cast(@pagesize * (@PageNo - 1) as varchar(50)) +' '+ @orderfield+' From '+ @tablename +' '+ @condition +' Order By '+ @orderfield +' Asc) Temp) Order By '+ @orderfield +' Asc'
end
end
exec (@SQL)
GO
(
@TableName varchar(100),@Fields varchar(8000),
@Condition varchar(8000),@OrderField varchar(50),
@OrderType varchar(4),@PageSize int,@PageNo int
) AS
--临时查询语句
declare @TempSql varchar(8000)
declare @PageLowerBound int, @PageUpperBound int
set @PageLowerBound=(@PageNo-1)*@PageSize
set @PageUpperBound=@PageLowerBound+@PageSize
--当记录数达到@PageUpperBound时就会停止处理查询
set rowcount @PageUpperBound
set nocount on
if (@PageNo<=1)
begin
if (@Condition='' or @Condition='None')
begin
set @TempSql='select top '+cast(@PageSize as varchar)+' '+ @Fields +' from '+@TableName
+' Order By '+ @OrderField +' '+@OrderType
end
else
begin
set @TempSql='select top '+cast(@PageSize as varchar)+' '+ @Fields +' from '+@TableName
+' '+@Condition+' Order By '+ @OrderField +' '+@OrderType
end
exec(@TempSql)end
else
begin
--建立临时表保存要查询的表(视图)的标识字段
CREATE table #indextable (TempID int PRIMARY key identity(1,1) not null,xValues int)
--只把id列取出放到临时表里
if (@Condition='' or @Condition='None')
begin
set @TempSql='insert into #indextable(xValues) select '+@OrderField+'
from '+@TableName+' Order By '+ @OrderField +' '+@OrderType
end
else
begin
set @TempSql='insert into #indextable(xValues) select '+@OrderField+'
from '+@TableName+' '+@Condition+' Order By '+ @OrderField +' '+@OrderType
end exec(@TempSql)
--只从表中取出所需要的记录,而不是所有的记录
set @TempSql='select '+@Fields+' from '+@TableName+',#indextable t
where '+@OrderField+'=t.xValues and t.TempID>'+cast(@PageLowerBound as varchar)+'
and t.TempID<='+cast(@PageUpperBound as varchar)+' order by t.TempID'
exec(@TempSql)
--print @TempSql
end
set nocount off
GO
--用途:分页存储过程(对有主键的表效率极高)
--说明:
------------------------------------CREATE PROCEDURE UP_GetRecordByPage
@tblName varchar(255), -- 表名
@fldName varchar(255), -- 主键字段名
@PageSize int = 10, -- 页尺寸
@PageIndex int = 1, -- 页码
@IsReCount bit = 0, -- 返回记录总数, 非 0 值则返回
@OrderType bit = 0, -- 设置排序类型, 非 0 值则降序
@strWhere varchar(1000) = '' -- 查询条件 (注意: 不要加 where)
ASdeclare @strSQL varchar(6000) -- 主语句
declare @strTmp varchar(100) -- 临时变量
declare @strOrder varchar(400) -- 排序类型if @OrderType != 0
begin
set @strTmp = '<(select min'
set @strOrder = ' order by [' + @fldName +'] desc'
end
else
begin
set @strTmp = '>(select max'
set @strOrder = ' order by [' + @fldName +'] asc'
endset @strSQL = 'select top ' + str(@PageSize) + ' * from ['
+ @tblName + '] where [' + @fldName + ']' + @strTmp + '(['
+ @fldName + ']) from (select top ' + str((@PageIndex-1)*@PageSize) + ' ['
+ @fldName + '] from [' + @tblName + ']' + @strOrder + ') as tblTmp)'
+ @strOrderif @strWhere != ''
set @strSQL = 'select top ' + str(@PageSize) + ' * from ['
+ @tblName + '] where [' + @fldName + ']' + @strTmp + '(['
+ @fldName + ']) from (select top ' + str((@PageIndex-1)*@PageSize) + ' ['
+ @fldName + '] from [' + @tblName + '] where ' + @strWhere + ' '
+ @strOrder + ') as tblTmp) and ' + @strWhere + ' ' + @strOrderif @PageIndex = 1
begin
set @strTmp =''
if @strWhere != ''
set @strTmp = ' where ' + @strWhere set @strSQL = 'select top ' + str(@PageSize) + ' * from ['
+ @tblName + ']' + @strTmp + ' ' + @strOrder
endif @IsReCount != 0
set @strSQL = 'select count(*) as Total from [' + @tblName + ']'+' where ' + @strWhereexec (@strSQL)GO
create proc mxw_proc
@pagesize int
as
begin
begin tran
declare @sqlstr nvarchar(500)
set @sqlstr='select top 10* from(select id ,email,ROW_NUMBER() OVER (ORDER BY Id) AS RowNo
FROM MemberReg )as a Where RowNo >'+cast(10*(@pagesize) as varchar) exec(@sqlstr)
if(@@error!=0)
begin
raiserror 20000 'mxw_proc error'
rollback tran
return (1)
end
commit tran
end
每页20条;
并已计算出有Y页;(确定排序方式为ID ASC);
要取第Z页(不是最后一页)的数据
则查询语句为"select * From (select Top 20 * From (select Top "+(20*Z).ToString()+" * From Table Order by ID ASC) Order by ID DESC) Order by ID Asc"
最外面一个select只为重新排序,中间两个查询是实际内容。
当Z=Y时要单独写。
"select Top 20 * From (select Top " + (X-(Y-1)*20).ToString()+ " FromTable Order by ID DESC) Order by ID ASC
//附:得到X的查询方法
select Count(*) From Table//详情请查询:http://www.gisshop.cn/blog/Blog.aspx?ID=4
数据查出来了,显示就应该很容易了吧...
这是最简单的分页方法