if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[sp_PageView]') and OBJECTPROPERTY(id, N'IsProcedure') = 1) drop procedure [dbo].[sp_PageView] GO/*--利用SQL未公开的存储过程实现分页方法简单且效率高,已知的问题就是要多返回一个空的记录集解决的方法是在前台调用时,用 set recordset=recordset.nextrecordset 的方法跳过第一个记录集此方法由J9988提供,我只是将它改成了方便调用的存储过程--邹建 2004.05(引用请保留此信息)--*//*--调用示例declare @PageCount int exec sp_PageView @sql='select * from sysobjects', @PageCurrent=2, @PageCount=@PageCount out SELECT @PageCount --*/ CREATE PROC sp_PageView @sql ntext, --要执行的sql语句 @PageCurrent int=1, --要显示的页码 @PageSize int=10, --每页的大小 @PageCount int OUTPUT --总页数 AS SET NOCOUNT ON DECLARE @p1 int --初始化分页游标 EXEC sp_cursoropen @cursor=@p1 OUTPUT, @stmt=@sql, @scrollopt=1, @ccopt=1, @rowcount=@PageCount OUTPUT--计算总页数 IF ISNULL(@PageSize,0)<1 SET @PageSize=10 SET @PageCount=(@PageCount+@PageSize-1)/@PageSize IF ISNULL(@PageCurrent,0)<1 OR ISNULL(@PageCurrent,0)>@PageCount SET @PageCurrent=1 ELSE SET @PageCurrent=(@PageCurrent-1)*@PageSize+1--显示指定页的数据 EXEC sp_cursorfetch @p1,16,@PageCurrent,@PageSize--关闭分页游标 EXEC sp_cursorclose @p1 GO
select top "+pagesize+" from table where id not in ( select top id "+pagesize*(pageindex-1)+" from table order by id) order by id /////// pagesize 表示一页显示多个数据 pageindex 表示当前是哪页
我忘记了说要在ASP.NET里啊.详细一点可以吗?
protected int PerPage = 10; private void Page_Load(object sender, System.EventArgs e) { if(!IsPostBack) { ViewState["CurrentPageNumber"]=1; LinkButton1.Enabled=false; LinkButton2.Enabled=false; LinkButton5.Enabled=false; LinkButton6.Enabled=false; bind(); radio(); } } void fenye() { LinkButton1.Enabled=(int)ViewState["CurrentPageNumber"]==1?false:true; LinkButton2.Enabled=(int)ViewState["CurrentPageNumber"]==1?false:true; LinkButton5.Enabled=(int)ViewState["CurrentPageNumber"]==1?false:true; LinkButton6.Enabled=(int)ViewState["CurrentPageNumber"]==1?false:true; LinkButton3.Enabled=(int)ViewState["CurrentPageNumber"]==(int)ViewState["PageCount"]?false:true; LinkButton4.Enabled=(int)ViewState["CurrentPageNumber"]==(int)ViewState["PageCount"]?false:true; LinkButton7.Enabled=(int)ViewState["CurrentPageNumber"]==(int)ViewState["PageCount"]?false:true; LinkButton8.Enabled=(int)ViewState["CurrentPageNumber"]==(int)ViewState["PageCount"]?false:true; } public void bind() { SqlConnection cn = new SqlConnection(ConfigurationSettings.AppSettings["SQLCONNECTIONSTRING"]); int newTop = PerPage * (int)ViewState["CurrentPageNumber"]; int oldTop = newTop - 10; string str = "select * from (select top "+newTop+" * from T_Balcony_DreamInfo order by PK_ID) a where a. PK_ID not in(select top "+oldTop+" PK_ID from T_Balcony_DreamInfo) order by PK_ID"; SqlCommand cm = new SqlCommand(str,cn); SqlDataAdapter da = new SqlDataAdapter(cm); DataSet dr = new DataSet(); da.Fill(dr); DataGrid1.DataSource=dr; DataGrid1.DataBind();
string SQLKey; if(PageNumber==1) //首页 { SQLKey="Select Top @PageSize @Field From @Table where @Where ORDER BY @PK DESC"; } else if(PageSize*PageNumber>AllCount) //最后的页 @@LeftSize { PageSize=AllCount-PageSize*(PageNumber-1); SQLKey="SELECT @Field FROM @Table WHERE @PK IN (SELECT Top @PageSize @PK FROM @Table where @Where ORDER BY @PK) ORDER BY @PK DESC"; SQLKey="SELECT @Field FROM @Table Where @PK IN (SELECT Top @LeftSize @PK FROM @Table where @Where ORDER BY @PK ASC) and @Where"; } // else //中间页 // { // //SQLKey="SELECT @Field FROM @Table Where @PK IN (SELECT Top @LeftSize @PK FROM @Table where @Where ORDER BY @PK ASC) and @Where"; // SQLKey="SELECT * FROM @Table WHERE @PK IN (SELECT Top @PageSize @PK FROM (SELECT Top @PageSize @Field FROM (SELECT Top @Page_Size_Number @PK FROM @Table where @Where ORDER BY @PK DESC) t1 ORDER BY @PK ASC) t2 ORDER BY @PK DESC)"; // }
邹健的过程 附过程 CREATE Proc cz_bbs_tiez @Source nvarchar(4000), --表名、视图名、查询语句 @Size int=10, --每页的大小(行数) @Index int=1, --要显示的页 @List nvarchar (1000)='', --要显示的字段列表,如果查询结果有标识字段,需要指定此值,且不包含标识字段 @Order nvarchar (1000)='', --排序字段列表 @Count int = null OUTPUT -- 输出记录数, 如果@Count为null, 则输出记录数, 否则不要输出 as set nocount on declare @FdName nvarchar(250) --表中的主键或表、临时表中的标识列名 ,@Id1 varchar(20),@Id2 varchar(20) --开始和结束的记录号 ,@Obj_ID int --对象ID --表中有复合主键的处理 declare @strfd nvarchar(2000) --复合主键列表 ,@strjoin nvarchar(4000) --连接字段 ,@strwhere nvarchar(2000) --查询条件 select @Obj_ID=object_id(@Source) ,@List=case isnull(@List,'') when '' then ' *' else ' '+@List end ,@Order=case isnull(@Order,'') when '' then '' else ' order by '+@Order end ,@Source=case when @Obj_ID is not null then ' '+@Source else ' ('+@Source+') a' end--如果显示第一页,可以直接用top来完成 if @Index=1 begin if @Count is null begin declare @lbtop1 nvarchar(1000) set @lbtop1 = 'select @Count = count(*) from '+@Source exec sp_executesql @lbtop1, N'@Count int out', @Count out end select @Id1=cast(@Size as varchar(20)) exec('select top '+@Id1+@List+' from '+@Source+@Order) return end--如果是表,则检查表中是否有标识更或主键 if @Obj_ID is not null and objectproperty(@Obj_ID,'IsTable')=1 begin select @Id1=cast(@Size as varchar(20)) ,@Id2=cast((@Index-1)*@Size as varchar(20)) select @FdName=name from syscolumns where id=@Obj_ID and status=0x80 if @@rowcount=0 --如果表中无标识列,则检查表中是否有主键 begin if not exists(select 1 from sysobjects where parent_obj=@Obj_ID and xtype='PK') goto lbusetemp --如果表中无主键,则用临时表处理 select @FdName=name from syscolumns where id=@Obj_ID and colid in( select colid from sysindexkeys where @Obj_ID=id and indid in( select indid from sysindexes where @Obj_ID=id and name in( select name from sysobjects where xtype='PK' and parent_obj=@Obj_ID ))) if @@rowcount>1 --检查表中的主键是否为复合主键 begin select @strfd='',@strjoin='',@strwhere='' select @strfd=@strfd+',['+name+']' ,@strjoin=@strjoin+' and a.['+name+']=b.['+name+']' ,@strwhere=@strwhere+' and b.['+name+'] is null' from syscolumns where id=@Obj_ID and colid in( select colid from sysindexkeys where @Obj_ID=id and indid in( select indid from sysindexes where @Obj_ID=id and name in( select name from sysobjects where xtype='PK' and parent_obj=@Obj_ID ))) select @strfd=substring(@strfd,2,2000) ,@strjoin=substring(@strjoin,5,4000) ,@strwhere=substring(@strwhere,5,4000) goto lbusepk end end end else goto lbusetemp/*--使用标识列或主键为单一字段的处理方法--*/ lbuseidentity: if @Count is null begin declare @lbuseidentity nvarchar(1000) set @lbuseidentity = 'select @Count = count(*) from '+@Source exec sp_executesql @lbuseidentity, N'@Count int out', @Count out end exec('select top '+@Id1+@List+' from '+@Source +' where '+@FdName+' not in(select top ' +@Id2+' '+@FdName+' from '+@Source+@Order +')'+@Order ) return/*--表中有复合主键的处理方法--*/ lbusepk: if @Count is null begin declare @lbusepk nvarchar(1000) set @lbusepk = 'select @Count = count(*) from '+@Source exec sp_executesql @lbusepk, N'@Count int out', @Count out end exec('select '+@List+' from(select top '+@Id1+' a.* from (select top 100 percent * from '+@Source+@Order+') a left join (select top '+@Id2+' '+@strfd+' from '+@Source+@Order+') b on '+@strjoin+' where '+@strwhere+') a' ) return/*--用临时表处理的方法--*/ lbusetemp: select @FdName='[ID_'+cast(newid() as varchar(40))+']' ,@Id1=cast(@Size*(@Index-1) as varchar(20)) ,@Id2=cast(@Size*@Index-1 as varchar(20))declare @lbusetemp nvarchar(4000) set @lbusetemp = 'select '+@FdName+'=identity(int,0,1),'+@List+' into #tb from'+@Source+@Order +case when @Count is null then ' set @Count = @@rowcount ' else '' end + ' select '+@List+' from #tb where '+@FdName+' between ' +@Id1+' and '+@Id2 exec sp_executesql @lbusetemp, N'@Count int out', @Count out GO
drop procedure [dbo].[sp_PageView]
GO/*--利用SQL未公开的存储过程实现分页方法简单且效率高,已知的问题就是要多返回一个空的记录集解决的方法是在前台调用时,用 set recordset=recordset.nextrecordset
的方法跳过第一个记录集此方法由J9988提供,我只是将它改成了方便调用的存储过程--邹建 2004.05(引用请保留此信息)--*//*--调用示例declare @PageCount int
exec sp_PageView
@sql='select * from sysobjects',
@PageCurrent=2,
@PageCount=@PageCount out
SELECT @PageCount
--*/
CREATE PROC sp_PageView
@sql ntext, --要执行的sql语句
@PageCurrent int=1, --要显示的页码
@PageSize int=10, --每页的大小
@PageCount int OUTPUT --总页数
AS
SET NOCOUNT ON
DECLARE @p1 int
--初始化分页游标
EXEC sp_cursoropen
@cursor=@p1 OUTPUT,
@stmt=@sql,
@scrollopt=1,
@ccopt=1,
@rowcount=@PageCount OUTPUT--计算总页数
IF ISNULL(@PageSize,0)<1
SET @PageSize=10
SET @PageCount=(@PageCount+@PageSize-1)/@PageSize
IF ISNULL(@PageCurrent,0)<1 OR ISNULL(@PageCurrent,0)>@PageCount
SET @PageCurrent=1
ELSE
SET @PageCurrent=(@PageCurrent-1)*@PageSize+1--显示指定页的数据
EXEC sp_cursorfetch @p1,16,@PageCurrent,@PageSize--关闭分页游标
EXEC sp_cursorclose @p1
GO
where id not in
( select top id "+pagesize*(pageindex-1)+" from table order by id)
order by id
///////
pagesize 表示一页显示多个数据
pageindex 表示当前是哪页
{
if(!IsPostBack)
{
ViewState["CurrentPageNumber"]=1;
LinkButton1.Enabled=false;
LinkButton2.Enabled=false;
LinkButton5.Enabled=false;
LinkButton6.Enabled=false;
bind();
radio();
}
}
void fenye()
{
LinkButton1.Enabled=(int)ViewState["CurrentPageNumber"]==1?false:true;
LinkButton2.Enabled=(int)ViewState["CurrentPageNumber"]==1?false:true;
LinkButton5.Enabled=(int)ViewState["CurrentPageNumber"]==1?false:true;
LinkButton6.Enabled=(int)ViewState["CurrentPageNumber"]==1?false:true;
LinkButton3.Enabled=(int)ViewState["CurrentPageNumber"]==(int)ViewState["PageCount"]?false:true;
LinkButton4.Enabled=(int)ViewState["CurrentPageNumber"]==(int)ViewState["PageCount"]?false:true;
LinkButton7.Enabled=(int)ViewState["CurrentPageNumber"]==(int)ViewState["PageCount"]?false:true;
LinkButton8.Enabled=(int)ViewState["CurrentPageNumber"]==(int)ViewState["PageCount"]?false:true;
}
public void bind()
{
SqlConnection cn = new SqlConnection(ConfigurationSettings.AppSettings["SQLCONNECTIONSTRING"]);
int newTop = PerPage * (int)ViewState["CurrentPageNumber"];
int oldTop = newTop - 10;
string str = "select * from (select top "+newTop+" * from T_Balcony_DreamInfo order by PK_ID) a where a. PK_ID not in(select top "+oldTop+" PK_ID from T_Balcony_DreamInfo) order by PK_ID";
SqlCommand cm = new SqlCommand(str,cn);
SqlDataAdapter da = new SqlDataAdapter(cm);
DataSet dr = new DataSet();
da.Fill(dr);
DataGrid1.DataSource=dr;
DataGrid1.DataBind();
cm.CommandText="select count(PK_ID) from T_Balcony_DreamInfo";
cn.Open();
int cout = (int)cm.ExecuteScalar();
cn.Close();
int PageCount = cout%PerPage==0?cout/PerPage:cout/PerPage+1;
ViewState["PageCount"]=PageCount;
int d = (int)ViewState["CurrentPageNumber"];
Label1.Text= "页码"+d+"/"+PageCount +" 总共"+cout+"条许愿";
Label2.Text= "页码"+d+"/"+PageCount +" 总共"+cout+"条许愿";
} private void LinkButton1_Click(object sender, System.EventArgs e)
{
ViewState["CurrentPageNumber"]=1;
fenye();
bind();
} private void LinkButton2_Click(object sender, System.EventArgs e)
{
ViewState["CurrentPageNumber"]=(int)ViewState["CurrentPageNumber"]-1;
fenye();
bind(); } private void LinkButton3_Click(object sender, System.EventArgs e)
{
ViewState["CurrentPageNumber"]=(int)ViewState["CurrentPageNumber"]+1;
fenye();
bind(); } private void LinkButton4_Click(object sender, System.EventArgs e)
{
ViewState["CurrentPageNumber"]=(int)ViewState["PageCount"];
fenye();
bind(); }
每次只取10条记录 减轻了重复大量访问数据 数据量大的话可以用这个~
using System;
using System.Data;
using System.Data.SqlClient;
using System.Text;namespace Backg
{
/// <summary>
/// 秋的分页
/// </summary>
public class PageF
{
#region 变量
private int _Page;
private int _pageSize;
private int _PageCount;
private string _Type;
private string _StrCount;
private string _strSql;
private string _TableName;
private string _SearchStr;
public DbObj da = new DbObj(); #endregion #region 属性
/// <summary>
/// 当前页
/// </summary>
public int Page
{
get
{
return _Page == 0 ? 1 : _Page;
}
set
{
_Page = value;
}
} /// <summary>
/// 每页记录的条数
/// </summary>
public int PageSize
{
get { return _pageSize == 0 ? 1 : _pageSize; }
set { _pageSize = value; }
}
/// <summary>
/// 总页数
/// </summary>
public int PageCount
{
get
{
_PageCount = Count / PageSize + ((Count % PageSize == 0) ? 0 : 1);
return _PageCount == 0 ? 1 : _PageCount;
}
}
/// <summary>
/// 查询参数
/// </summary>
public string Type
{
get
{
return _Type;
}
set
{
_Type = value;
}
} /// <summary>
/// 过滤参数
/// </summary>
private string StrReplace(string Str)
{
string StrRpc;
try
{
StrRpc = Str.Replace("'", "");
}
catch
{
StrRpc = Str;
}
return StrRpc;
}
/// <summary>
/// 统计记录条数
/// </summary> public int Count
{
get
{
_StrCount = "SELECT COUNT(TableId) FROM " + this.TableName + "";
if (Type.Length > 0)
{
_StrCount = _StrCount +" where 1<>0 " + Type;
} int i = 0;
i = (int)da.ExeSql(_StrCount, true);
return i;
}
} /// <summary>
/// 构建查询SQL
/// </summary>
public string TableSql
{
get
{
if (this.Page == 0 || this.Page == 1)
{
if (this.Type.Length > 0)
{
_strSql = "select top " + this.PageSize + " * from " + TableName +" where 1<> 0 "+ this.Type + " order by [TableId] desc ";
}
else
{
_strSql = "select top " + this.PageSize + " * from " + TableName + " order by [TableId] desc ";
}
}
else
{
if (this.Type.Length > 0)
{
Int32 p = (Page - 1) * PageSize;
_strSql = "select top "+this.PageSize+" * from " + TableName + " where 1<> 0 " + this.Type + " and [TableId] not in (select top " + p + " [TableId] from " + TableName + " where 1<> 0 " + this.Type + " order by [TableId] desc) order by [TableId] desc";
}
else
{
Int32 p = (Page - 1) * PageSize;
_strSql = "select top "+this.PageSize+" * from " + TableName + " where [TableId] not in (select top " + p + " TableId from " + TableName + " order by [TableId] desc) order by [TableId] desc";
}
}
return _strSql;
}
} /// <summary>
/// 表名
/// </summary>
public string TableName
{
get
{
return _TableName;
}
set
{
_TableName = value;
}
}
public string SearchStr()
{ if (this.Type == "")
{
_SearchStr = this.StrTurnPage();
}
else
{
_SearchStr = this.StrSerchPage();
}
return _SearchStr; }
/// <summary>
/// 返回分页
/// </summary>
/// <returns></returns>
public string StrTurnPage()
{
string pagetext = null;
pagetext = string.Format("共{0}页,{1}条/页,共{2}条,", this.PageCount, PageSize, this.Count);
//StringBuilder pagetext = new StringBuilder();
// pagetext.Append(string.Format("共{0}页,{1}条/页,共{2}条,",this.PageCount, PageSize ,this.Count)); string pagetemp = ""; if (this.Page == 1)
{
pagetemp = string.Format("首页 上一页 <a href='?page=2'>下一页</a> <a href='?page={0}'>尾页</a>", this.PageCount);
}
if (this.Page == this.PageCount)
{
pagetemp = string.Format("<a href='?page=1'>首页</a> <a href='?page={0}'>上一页</a> 下一页 尾页", this.PageCount - 1);
}
if ((this.Page != 1) && (this.Page != this.PageCount))
{
pagetemp = string.Format("<a href='?page=1'>首页</a> <a href='?page={0}'>上一页</a> <a href='?page={1}'>下一页</a> <a href='?page={2}'>尾页</a>", this.Page - 1, this.Page + 1, this.PageCount);
}
if (this.PageCount == 1)
{
pagetemp = "首页 上一页 下一页 尾页";
} return pagetext + pagetemp;
// return pagetext;
}
/// <summary>
/// 有查询字符串的
/// </summary>
/// <returns></returns>
public string StrSerchPage()
{
string pagetext = null;
pagetext = string.Format("共{0}页,{1}条/页,共{2}条,", this.PageCount, PageSize, this.Count); string pagetemp = ""; if (this.Page == 1)
{
pagetemp = string.Format("首页 上一页 <a href='?type={1}&page=2'>下一页</a> <a href='?type={1}&page={0}'>尾页</a>", this.PageCount, this.Type);
}
if (this.Page == this.PageCount)
{
pagetemp = string.Format("<a href='?type={1}&page=1'>首页</a> <a href='?type={1}&page={0}'>上一页</a> 下一页 尾页", this.PageCount - 1, this.Type);
}
if ((this.Page != 1) && (this.Page != this.PageCount))
{
pagetemp = string.Format("<a href='?type={3}&page=1'>首页</a> <a href='?type={3}&page={0}'>上一页</a> <a href='?type={3}&page={1}'>下一页</a> <a href='?type={3}&page={2}'>尾页</a>", this.Page - 1, this.Page + 1, this.PageCount, this.Type);
}
if (this.PageCount == 1)
{
pagetemp = "首页 上一页 下一页 尾页";
}
return pagetext + pagetemp;
}
#endregion
}
}
LinkButton2 上一页
LinkButton3 下一页
LinkButton4 末页
private PageF myFen = new PageF();
private DbObj db = new DbObj();
public Int32 page = 0;
protected void Page_Load(object sender, EventArgs e)
{
if (!IsPostBack)
{
this.SetList();
}
} private void SetList()
{ page = Convert.ToInt32(Request.QueryString["page"]);
myFen.Page = page;
myFen.PageSize = 20;
myFen.Type = "";
myFen.TableName = "BL_ClassifyTag";
this.Label1.Text = myFen.StrTurnPage();
db.DataBindStr(ListRep, myFen.TableSql);
}
if(PageNumber==1) //首页
{
SQLKey="Select Top @PageSize @Field From @Table where @Where ORDER BY @PK DESC";
}
else if(PageSize*PageNumber>AllCount) //最后的页 @@LeftSize
{
PageSize=AllCount-PageSize*(PageNumber-1);
SQLKey="SELECT @Field FROM @Table WHERE @PK IN (SELECT Top @PageSize @PK FROM @Table where @Where ORDER BY @PK) ORDER BY @PK DESC";
SQLKey="SELECT @Field FROM @Table Where @PK IN (SELECT Top @LeftSize @PK FROM @Table where @Where ORDER BY @PK ASC) and @Where";
}
// else //中间页
// {
// //SQLKey="SELECT @Field FROM @Table Where @PK IN (SELECT Top @LeftSize @PK FROM @Table where @Where ORDER BY @PK ASC) and @Where";
// SQLKey="SELECT * FROM @Table WHERE @PK IN (SELECT Top @PageSize @PK FROM (SELECT Top @PageSize @Field FROM (SELECT Top @Page_Size_Number @PK FROM @Table where @Where ORDER BY @PK DESC) t1 ORDER BY @PK ASC) t2 ORDER BY @PK DESC)";
// }
附过程
CREATE Proc cz_bbs_tiez
@Source nvarchar(4000), --表名、视图名、查询语句
@Size int=10, --每页的大小(行数)
@Index int=1, --要显示的页
@List nvarchar (1000)='', --要显示的字段列表,如果查询结果有标识字段,需要指定此值,且不包含标识字段
@Order nvarchar (1000)='', --排序字段列表
@Count int = null OUTPUT -- 输出记录数, 如果@Count为null, 则输出记录数, 否则不要输出
as
set nocount on
declare @FdName nvarchar(250) --表中的主键或表、临时表中的标识列名
,@Id1 varchar(20),@Id2 varchar(20) --开始和结束的记录号
,@Obj_ID int --对象ID
--表中有复合主键的处理
declare @strfd nvarchar(2000) --复合主键列表
,@strjoin nvarchar(4000) --连接字段
,@strwhere nvarchar(2000) --查询条件
select @Obj_ID=object_id(@Source)
,@List=case isnull(@List,'') when '' then ' *' else ' '+@List end
,@Order=case isnull(@Order,'') when '' then '' else ' order by '+@Order end
,@Source=case when @Obj_ID is not null then ' '+@Source else ' ('+@Source+') a' end--如果显示第一页,可以直接用top来完成
if @Index=1
begin
if @Count is null
begin
declare @lbtop1 nvarchar(1000)
set @lbtop1 = 'select @Count = count(*) from '+@Source
exec sp_executesql @lbtop1, N'@Count int out', @Count out
end
select @Id1=cast(@Size as varchar(20))
exec('select top '+@Id1+@List+' from '+@Source+@Order)
return
end--如果是表,则检查表中是否有标识更或主键
if @Obj_ID is not null and objectproperty(@Obj_ID,'IsTable')=1
begin
select @Id1=cast(@Size as varchar(20))
,@Id2=cast((@Index-1)*@Size as varchar(20)) select @FdName=name from syscolumns where id=@Obj_ID and status=0x80
if @@rowcount=0 --如果表中无标识列,则检查表中是否有主键
begin
if not exists(select 1 from sysobjects where parent_obj=@Obj_ID and xtype='PK')
goto lbusetemp --如果表中无主键,则用临时表处理 select @FdName=name from syscolumns where id=@Obj_ID and colid in(
select colid from sysindexkeys where @Obj_ID=id and indid in(
select indid from sysindexes where @Obj_ID=id and name in(
select name from sysobjects where xtype='PK' and parent_obj=@Obj_ID
)))
if @@rowcount>1 --检查表中的主键是否为复合主键
begin
select @strfd='',@strjoin='',@strwhere=''
select @strfd=@strfd+',['+name+']'
,@strjoin=@strjoin+' and a.['+name+']=b.['+name+']'
,@strwhere=@strwhere+' and b.['+name+'] is null'
from syscolumns where id=@Obj_ID and colid in(
select colid from sysindexkeys where @Obj_ID=id and indid in(
select indid from sysindexes where @Obj_ID=id and name in(
select name from sysobjects where xtype='PK' and parent_obj=@Obj_ID
)))
select @strfd=substring(@strfd,2,2000)
,@strjoin=substring(@strjoin,5,4000)
,@strwhere=substring(@strwhere,5,4000)
goto lbusepk
end
end
end
else
goto lbusetemp/*--使用标识列或主键为单一字段的处理方法--*/
lbuseidentity:
if @Count is null
begin
declare @lbuseidentity nvarchar(1000)
set @lbuseidentity = 'select @Count = count(*) from '+@Source
exec sp_executesql @lbuseidentity, N'@Count int out', @Count out
end
exec('select top '+@Id1+@List+' from '+@Source
+' where '+@FdName+' not in(select top '
+@Id2+' '+@FdName+' from '+@Source+@Order
+')'+@Order
)
return/*--表中有复合主键的处理方法--*/
lbusepk:
if @Count is null
begin
declare @lbusepk nvarchar(1000)
set @lbusepk = 'select @Count = count(*) from '+@Source
exec sp_executesql @lbusepk, N'@Count int out', @Count out
end exec('select '+@List+' from(select top '+@Id1+' a.* from
(select top 100 percent * from '+@Source+@Order+') a
left join (select top '+@Id2+' '+@strfd+'
from '+@Source+@Order+') b on '+@strjoin+'
where '+@strwhere+') a'
)
return/*--用临时表处理的方法--*/
lbusetemp:
select @FdName='[ID_'+cast(newid() as varchar(40))+']'
,@Id1=cast(@Size*(@Index-1) as varchar(20))
,@Id2=cast(@Size*@Index-1 as varchar(20))declare @lbusetemp nvarchar(4000)
set @lbusetemp = 'select '+@FdName+'=identity(int,0,1),'+@List+'
into #tb from'+@Source+@Order
+case when @Count is null then ' set @Count = @@rowcount ' else '' end + '
select '+@List+' from #tb where '+@FdName+' between '
+@Id1+' and '+@Id2
exec sp_executesql @lbusetemp, N'@Count int out', @Count out
GO