怎样用存储过程写分页？（100分相送）

我现在的通用的存储过程分页除了select，from,where,order by外其余的都是在存储过程动态拼凑，当然效率可能低了点

谢谢hertcloud，这个我也有，能用的话，我早就用了：）

谁有这方面的存储过程，能发给我一份吗？
问题解决结贴~~
[email protected]

建立一个表变量或临时表，表列与你的a.XXX,b.YYY对应，参考下面的示例，虽然它只有一个表但方法一样。利用自定义分页技术提高数据库性能
http://dotnet.aspx.cc/ShowDetail.aspx?id=108B1516-53CE-4357-B061-17295AF9689F

谢谢CMIC(大象)，我看过这个例子，但是我有个问题想请教你，怎样自动分配内存表的列呢？
比如说，有时这个表有5列，有时更多。。我知道肯定是传这样一个参数
比如："id，name,age,sex",但是我怎样在存储过程里改成
declare @t_table table
(
[id] [int] IDENTITY (1, 1) Primary key NOT NULL ,
[name] [varchar] (40) ,
[age] [varchar] (20) ,
[sex] [varchar] (40) ,
........
)

Create  PROCEDURE ListPage(
@tblName nvarchar(200),          ----要显示的表或多个表的连接
@fldName nvarchar(200) = '*',          ----要显示的字段列表
@pageSize int = 10,                   ----每页显示的记录个数
@page int = 1,          ----要显示那一页的记录
@pageCount int = 1 output,          ----查询结果分页后的总页数
@Counts int = 1 output,          ----查询到的记录数
@fldSort nvarchar(100) = null,          ----排序字段列表或条件
@Sort bit = 0,          ----排序方法，0为升序，1为降序
@strCondition nvarchar(200) = null, ----查询条件,不需where
@ID nvarchar(50) ----主表的主键
)
AS
SET NOCOUNT ON
Declare @sqlTmp nvarchar(1000) ----存放动态生成的SQL语句
Declare @strTmp nvarchar(1000) ----存放取得查询结果总数的查询语句
Declare @strID nvarchar(1000) ----存放取得查询开头或结尾ID的查询语句
Declare @sqlSort nvarchar(200) ----存放临时生成的排序条件
Declare @intCounts int ----要移动的记录数
Declare @BeginID int ----开始的ID
Declare @EndID   int ----结束的ID--------首先生成排序方法---------
if @Sort=0 --升序
begin
  if not(@fldSort is null)
set @sqlSort = ' Order by ' + @fldSort
  else
set @sqlSort = ' Order by ' + @ID
end
else --降序
begin
if not(@fldSort is null)
set @sqlSort = ' Order by ' + @fldSort + ' DESC'
else
set @sqlSort = ' Order by ' + @ID + ' DESC '
end
--------生成查询语句--------
--此处@strTmp为取得查询结果数量的语句
if @strCondition is null --没有设置显示条件
begin
set @sqlTmp =  @fldName + ' From ' + @tblName
set @strTmp = 'select @Counts=Count(' + @ID + ') FROM '+@tblName
set @strID = ' From ' + @tblName
end
else
begin
set @sqlTmp = + @fldName + 'From ' + @tblName + ' where ' + @strCondition
set @strTmp = 'select @Counts=Count(' + @ID + ') FROM '+@tblName + ' where ' + @strCondition
set @strID = ' From ' + @tblName + ' where ' + @strCondition
end

----取得查询结果总数量-----
exec sp_executesql @strTmp,N'@Counts int out ',@Counts out--取得分页总数
if @Counts <= @pageSize
set @pageCount = 1
else
set @pageCount = (@Counts / @pageSize) + 1
--计算要移动的记录数
if @page = 1
set @intCounts = @pageSize
else
begin
set @intCounts = (@page-1) * @pageSize + 1
end-----取得分页后此页的第一条记录的ID
set @strID = 'select @BeginID=' + @ID + ' ' + @strIDset @intCounts = @intCounts - @pageSize + 1
set rowcount  @intCounts
exec sp_executesql @strID,N'@BeginID int out ',@BeginID out-----取得分页后此页的最后一条记录的ID
set @intCounts = @intCounts + @pageSize - 1
print @intCounts
set rowcount  @intCounts
exec sp_executesql @strID,N'@BeginID int out ',@EndID out
------恢复系统设置-----
set rowcount  0
SET NOCOUNT OFF------返回查询结果-----
if @strCondition is null
set @strTmp = 'select ' + @sqlTmp + ' where ' + @ID + ' between ' + str(@BeginID) + ' and ' + str(@EndID)
else
set @strTmp = 'select ' + @sqlTmp + ' where ' + @ID +' (between ' + str(@BeginID) + ' and ' + str(@EndID) + ')  and ' + @strConditionif not(@sqlSort is null)
set @strTmp = @strTmp + @sqlSort
exec sp_executesql @strTmpGO

if exists(select * from sysobjects where id = object_id('common_page'))
drop proc common_page
goCREATE PROCEDURE common_page
@page INT=1,                  --页数
@pagecount INT=20,            --每页显示数据数
@fields nvarchar(800),        --显示的字段名
@condition nvarchar(2000),    --条件
@order nvarchar(500)         --排序字段
--with encryption
ASdeclare @SQLstr nvarchar(4000)
declare @opp nvarchar(500)
declare @endcount BIGINT
declare @maxcount INT
declare @endcoding INT
declare @endtime datetimeset nocount on   set @SQLstr='set @cc=(select count(1) ' + @condition + ')'
   EXECUTE sp_executesql @SQLstr ,N'@cc INT output',@maxcount output
   if @maxcount<=0
      return (@maxcount)   set @endcount=@pagecount * (@page)
   if @endcount > @maxcount set @endcount=@maxcount   set @opp=replace(@order,' asc',' myasc')
   set @opp=replace(@opp,' desc',' asc')
   set @opp=replace(@opp,' myasc', ' desc')
         if @page<=1
        BEGIN
        set @SQLstr ='select top ' + cast(@pagecount as nvarchar(15)) + ' ' + @fields + ' ' + @condition + ' order by ' + @order
        EXECUTE sp_executesql @SQLstr
        END

   else if @endcount > @maxcount /2
        BEGIN
        set @endcount=@maxcount-@endcount+@pagecount
        set @SQLstr ='select top ' + cast(@pagecount as nvarchar(15)) +' * from (select top ' + cast(@endcount as nvarchar(15)) + ' ' + @fields + ' ' + @condition + ' order by ' + @opp +') as s1 order by ' + @order
        EXECUTE sp_executesql @SQLstr
        END   else BEGIN
        set @SQLstr ='select * from (select top ' + cast(@pagecount as nvarchar(15)) +' * from (select top ' + cast(@endcount as nvarchar(15)) + ' ' + @fields + ' ' + @condition + ' order by ' + @order +') as s1 order by ' + @opp +' ) as s2 order by ' + @order
        EXECUTE sp_executesql @SQLstr
        END   return(@maxcount)

set nocount off
GO

<%@ Page Language="C#"%>
            <%@ Import Namespace="System.Data"%>
            <%@Import Namespace="System.Data.SqlClient"%>
            <%@Import Namespace="System.Configuration"%>
            <%@Register TagPrefix="Webdiyer" Namespace="Wuqi.Webdiyer" Assembly="aspnetpager"%>
            <HTML>
            <HEAD>
            <TITLE>Welcome to Webdiyer.com </TITLE>
              <script runat="server">
             SqlConnection conn;
             SqlCommand cmd;
             void Page_Load(object src,EventArgs e)
             {
             conn=new SqlConnection(ConfigurationSettings.AppSettings["ConnStr"]);
             if(!Page.IsPostBack)
             {
             cmd=new SqlCommand("GetNews",conn);
             cmd.CommandType=CommandType.StoredProcedure;
             cmd.Parameters.Add("@pageindex",1);
             cmd.Parameters.Add("@pagesize",1);
             cmd.Parameters.Add("@docount",true);
             conn.Open();
             pager.RecordCount=(int)cmd.ExecuteScalar();
             conn.Close();
             BindData();
             }
             }

             void BindData()
             {
             cmd=new SqlCommand("GetNews",conn);
             cmd.CommandType=CommandType.StoredProcedure;
             cmd.Parameters.Add("@pageindex",pager.CurrentPageIndex);
             cmd.Parameters.Add("@pagesize",pager.PageSize);
             cmd.Parameters.Add("@docount",false);
             conn.Open();
             dataGrid1.DataSource=cmd.ExecuteReader();
             dataGrid1.DataBind();
             conn.Close();
             pager.CustomInfoText="记录总数：<font color=\"blue\"><b>"+pager.RecordCount.ToString()+"</b></font>";
             pager.CustomInfoText+=" 总页数：<font color=\"blue\"><b>"+pager.PageCount.ToString()+"</b></font>";
             pager.CustomInfoText+=" 当前页：<font color=\"red\"><b>"+pager.CurrentPageIndex.ToString()+"</b></font>";
             }
             void ChangePage(object src,PageChangedEventArgs e)
             {
             pager.CurrentPageIndex=e.NewPageIndex;
             BindData();
             }
              </script>
                 <meta http-equiv="Content-Language" content="zh-cn">
             <meta http-equiv="content-type" content="text/html;charset=gb2312">
             <META NAME="Generator" CONTENT="EditPlus">
             <META NAME="Author" CONTENT="Webdiyer([email protected])">
             </HEAD>
             <body>
             <form runat="server" ID="Form1">
             <asp:DataGrid id="dataGrid1" runat="server" />

             <Webdiyer:AspNetPager id="pager"
             runat="server"
             PageSize="8"
             NumericButtonCount="8"
             ShowCustomInfoSection="left"
             PagingButtonSpacing="0"
             ShowInputBox="always"
             CssClass="mypager"
             HorizontalAlign="right"
             OnPageChanged="ChangePage"
             SubmitButtonText="转到"
             NumericButtonTextFormatString="[{0}]"/>

             </form>
             </body>
            </HTML>

下面是该示例所用的Sql Server存储过程：            CREATE procedure GetNews
              (@pagesize int,
             @pageindex int,
             @docount bit)
             as
             set nocount on
             if(@docount=1)
             select count(id) from news
             else
             begin
             declare @indextable table(id int identity(1,1),nid int)
             declare @PageLowerBound int
             declare @PageUpperBound int
             set @PageLowerBound=(@pageindex-1)*@pagesize
             set @PageUpperBound=@PageLowerBound+@pagesize
             set rowcount @PageUpperBound
             insert into @indextable(nid) select id from news order by addtime desc
             select O.id,O.source,O.title,O.addtime from news O,@indextable t where O.id=t.nid
             and t.id>@PageLowerBound and t.id<=@PageUpperBound order by t.id
             end
             set nocount off
            GO

调试易

怎样用存储过程写分页？（100分相送）

解决方案 »