分页问题(速度超慢)
表中数据才12万条多点
以前是取出所有数据，在页面中分页的，可时常出现out of memory
现在改用存储过程分页，内存溢出问题已经解决了，没发生过，但却出现SQL超时：Microsoft OLE DB Provider for SQL Server error '80040e31 ' Timeout expired
查了资料，找了原因，，但还是没有解决。
情况是：前面的页面一切正常，只要浏览超过3000左右的页面时，就出现超时
在分析器里执行远程的那个存储过程的结果：
当前页 执行时间(分)
1 00:1
10 00:1
100 00:1
500 00:5
1000 00:8
1500 00:17
200000:25
300000:51
400001:25
500002:11
如果查询到最后一页，，好久都没结果出来(等了2分多钟没等下去，就取消了)
存储过程中主要就2条查询语句：
SELECT COUNT(1) FROM tb_actual WHERE ( DeletedFlag=0 and ActualType = 0 and TradeID=0 and keyword not like N '%yd% ' and ((isnull(Actualname,N ' ')+isnull(address,N ' ')+isnull(companyname,N ' ')+isnull(keyword,N ' ')) like N '%广州% ' ) )
SELECT Top 10 ID,ActualName,AgentCode,....... FROM tb_actual WHERE ID NOT IN (SELECT TOP 112180 ID FROM tb_actual WHERE ( DeletedFlag=0 and ActualType = 0 and TradeID=0 and keyword not like N '%yd% ' and ((isnull(Actualname,N ' ')+isnull(address,N ' ')+isnull(companyname,N ' ')+isnull(keyword,N ' ')) like N '%广州% ' ) ) Order By ID Desc) AND ( DeletedFlag=0 and ActualType = 0 and TradeID=0 and keyword not like N '%yd% ' and ((isnull(Actualname,N ' ')+isnull(address,N ' ')+isnull(companyname,N ' ')+isnull(keyword,N ' ')) like N '%广州% ' ) ) Order By ID Desc
我所想到的：
1、MSSQL属性中的 "连接 "选项卡 "查询超时设定 "为默认的600秒，我想已经够大了
2、表中自增ID,条件中的字段都建了索引。
存储过程主要就这些语句：
CREATE PROCEDURE [dbo].[sp_Wap_ActualSplitPage]
@tblName NVARCHAR(500)=N 'tb_actual ',--表，视图,查询语句
@fldNames NVARCHAR(1000)=N '* ',--要显示的字段列表
@PageSizeINT=10,--每页显示的记录数
@curPageBIGINT=1 OUTPUT,--当前页
@TotalRecordNum BIGINT=0 OUTPUT,--总记录数
@fldOrderVARCHAR(255),--关键字段
@strWhere NVARCHAR(1000) = N ' ', --查询条件
@strOrder NVARCHAR(1000) = N ' ', --设置排序
.......
AS
set nocount on
declare @InsideWhere nvarchar(4000)
DECLARE @strSQL NVARCHAR(4000),@strTmpSQL NVARCHAR(2000),@intTmpRecordNum INT,@intTmpTotalPage INT
Select @strWhere=CASE ISNULL(@strWhere,N ' ') WHEN N ' ' THEN N ' 1=1 ' ELSE @strWhere END
Select @strOrder=CASE ISNULL(@strOrder,N ' ') WHEN N ' ' THEN N ' ' Else N ' Order By '+@strOrder END
--把表中的记录总数返回给变量@intTmpRecordNum
Set @strTmpSQL = N 'SELECT @intTmpRecordNum=COUNT(1) FROM '+@tblName+N ' WHERE '+@strWhere
Exec sp_executesql @strTmpSQL,N '@intTmpRecordNum int out ',@intTmpRecordNum out
Set @TotalRecordNum=@intTmpRecordNum
IF @intTmpRecordNum%@PageSize=0
SET @intTmpTotalPage = @intTmpRecordNum/@PageSize
ELSE
SET @intTmpTotalPage = @intTmpRecordNum/@PageSize+1
IF @curPage> @intTmpTotalPage
SET @curPage=@intTmpTotalPage
IF @intTmpRecordNum=0
Set @curPage = 1
IF @curPage <=1
BEGIN
Set @curPage = 1
Set @strSQL = N 'SELECT Top '+cast(@PageSize as nvarchar)+N ' '+@fldNames+N ' FROM '+@tblName+N ' WHERE '+@strWhere+N ' '+@strOrder
END
ELSE
BEGIN
SET @strSQL=N 'SELECT Top '+cast(@PageSize as nvarchar)+N ' '+@fldNames+N ' FROM '+@tblName+N ' WHERE '+Cast(@fldOrder as nvarchar)+N ' NOT IN (SELECT TOP '+cast((@curPage-1)*@PageSize as nvarchar)+N ' '+Cast(@fldOrder as nvarchar)+N ' FROM '+@tblName+N ' WHERE '+@strWhere+N ' '+@strOrder+N ') AND '+@strWhere+N ' '+@strOrder
END
--print @strSQL
--Set @strReturn = @strSQL
Select @curPage as curPage,@TotalRecordNum as TotalRecordNum
Exec(@strSQL)
[解决办法]
不懂，帮你顶
[解决办法]
你这里有许多非常糟糕的东西
比如like not like应用，而且在count和具体查询还都用到了，也就是至少用到了2次模糊查找
建议修改，应用临时表会比你这个强很多，至少模糊匹配只需要一次
count计算很耗费资源的
[解决办法]
先打上SP4再说
[解决办法]
主键?
视图？
最近经常看到你上网啊．．．呵呵
[解决办法]
顶
[解决办法]
去看我推荐的分页存储过程 http://www.blog.163.com/feb-/
[解决办法]
语句中多处使用LIKE,IN,ISNULL ==,这些都是检索时影响速度的
所以，你要尽可能的去简化你的SQL语句,还有,如果表设计的不合理,在一定的考虑范围内,可以考虑做结构的调整(当然改动比较大,但处于整体性能,有必要就改一下).
[解决办法]
也有可能是有些关键字没有过滤，或者你的某一数据太长造成的排序时间延长
[解决办法]
我的数据也是10多万条，用的是临时表分页存储，挺快的。
[解决办法]
学习!
[解决办法]
关注
[解决办法]
SELECT COUNT(1) FROM tb_actual WHERE ( DeletedFlag=0 and ActualType = 0 and TradeID=0 and keyword not like N '%yd% ' and ((isnull(Actualname,N ' ')+isnull(address,N ' ')+isnull(companyname,N ' ')+isnull(keyword,N ' ')) like N '%广州% ' ) )
-*****************************************************************************--
isnull(Actualname,N ' ')+isnull(address,N ' ')+isnull(companyname,N ' ')+isnull(keyword,N ' ')) like N '%广州% '
---------------> Actualname like '%广州% ' or address like '%广州% ' or companyname like '%广州% ' or keyword like '%广州% '
SELECT Top 10 ID,ActualName,AgentCode,....... FROM tb_actual WHERE ID NOT IN (SELECT TOP 112180 ID FROM tb_actual WHERE ( DeletedFlag=0 and ActualType = 0 and TradeID=0 and keyword not like N '%yd% ' and ((isnull(Actualname,N ' ')+isnull(address,N ' ')+isnull(companyname,N ' ')+isnull(keyword,N ' ')) like N '%广州% ' ) ) Order By ID Desc) AND ( DeletedFlag=0 and ActualType = 0 and TradeID=0 and keyword not like N '%yd% ' and ((isnull(Actualname,N ' ')+isnull(address,N ' ')+isnull(companyname,N ' ')+isnull(keyword,N ' ')) like N '%广州% ' ) ) Order By ID Desc
-***********************************************************-
select top 10 ID,ActualName,...... from
(select top 112190 ID,ActualName,.... from tb_ctual
where ......
) aa
order by ID Desc
[解决办法]
几点建议：
（1）你的分页存储过程效率不高，竟然出现了not in，这样的语句效率是很低的，写这样存储过程分页的作者本身sql水平不会太高，建议换一个存储过程；
（2）在连接和经常查询的字段建立索引；
（3）优化你的语句，尽量少用 in ，not in语句
附(修改的网上通用分页存储过程):
/*
-----存储过程分页
-----author：yuemb
*/
CREATE PROCEDURE [dbo].[Proc_SearchBasePlus]
@TableName varchar(1000), -- 表名
@BeginIndex int = 0, ------搜索开始的记录索引
@SelectFieldName varchar(4000)= ' ', -- 要显示的字段名(不要加select)
@StrWhere varchar(4000)= ' ', -- 查询条件(注意: 不要加 where)
@OrderFieldName varchar(255)= ' ', -- 排序索引字段名
@OrderWhere varchar(4000)= ' ', -- 排序附加
@PageSize int=0 , -- 页大小,如果也大小为0,那么将搜索(@iRowCount - @BeginIndex)的记录数
@PageIndex int = 1, -- 页码,页码小于0 时,赋为1
@OrderType int = 0 , -- 设置排序类型, 非 0 值则降序
@iRowCount int=0 output , -- 返回记录总数
@pageCount int=0 output -- 返回记录总数
AS
declare @strSQL nvarchar(4000) -- 主语句
declare @addAnd nvarchar(20) -- 'wher ' + 'and '
declare @strSelect nvarchar(4000) --select 关键字,如果@PageSize=0,那么 'select ' ,其他 'select top '
declare @strTmp nvarchar(4000) -- 临时变量
declare @strOrder nvarchar(400) -- 排序类型
declare @strRowCount nvarchar(4000) -- 用于查询记录总数的语句
set @BeginIndex = @BeginIndex -1
set @OrderFieldName=lower(ltrim(rtrim(@OrderFieldName)))
set @SelectFieldName=lower(ltrim(rtrim(@SelectFieldName)))
if @BeginIndex <1
BEGIN
set @BeginIndex=0
END
if @SelectFieldName = ' ' --------------如果@PageIndex 应大于 0
begin
set @SelectFieldName = ' * '
end
if @PageIndex <= 0 or @PageIndex = null --------------如果@PageIndex 应大于 0
begin
set @PageIndex = 1
end
declare @oddindex int
set @oddindex = charindex( ', ',@OrderFieldName)
if @oddindex > 0
begin
set @OrderFieldName=left(@OrderFieldName,@oddindex-1)
end
if @OrderType != 0
begin
set @strTmp = ' <(select min '
set @strOrder = ' order by ' + @OrderFieldName + ' desc '
end
else
begin
set @strTmp = '> (select max '
set @strOrder = ' order by ' + @OrderFieldName + ' asc '
end
if @OrderFieldName = ' ' ------------- @OrderFieldName为空,那么 不存在 order 不要 子句
begin
set @strOrder = ' '
end
---------------------------如果传入多个order字段,那么截取第一------------------------------------
------------------------
---------------------获取总记录数------------------------------
if @strWhere!= ' '
begin
set @strRowCount = ' select @iRowCount=count(*) from ' + @tableName+ ' where '+ ' ( ' + @strWhere + ') '
end
else
begin
set @strRowCount = 'select @iRowCount=count(*) from ' + @tableName
end
exec sp_executesql @strRowCount,N '@iRowCount int out ',@iRowCount output ----------------获取从总记录数
print '@iRowCount= ' + str(@iRowCount)
------------------------------------
---------------------------------
if @iRowCount < @BeginIndex
begin
set @strSQL = 'select top 0 ' + @SelectFieldName + ' from ' + @TableName
goto end_pro
end
--------------------------------
if @PageSize = 0 or @PageSize = null --------------如果pazesize为 0 或 空 ,那么属于不分页的情况,只需提取从@BeginIndex开始的部分,@PageSize
begin
set @strSelect = 'select '
set @PageIndex = 1
end
else
begin
set @strSelect = 'select top ' + str(@PageSize)
end
if (@BeginIndex > 0) and (@OrderFieldName= 'newid() ') or (@OrderFieldName= ' ') and (@PageSize = 0)
begin
if (@iRowCount> =@BeginIndex)
begin
set @strSelect = 'select top ' + str(@iRowCount-@BeginIndex) + ' '
end
end
if(@PageIndex = 1 and @BeginIndex = 0) or (@OrderFieldName= 'newid() ') or (@OrderFieldName= ' ') --------------min,max函数体内的select关键字,如果 @pageindex和@BeginIndex 都为0或者by newid()(*随机抽取*),那么不存在 <或> 小于子句,
begin
set @strSQL = @strSelect+ ' ' + @SelectFieldName+ ' from '
+ @tableName
set @addAnd = ' where '
end
else
begin
set @strSQL = @strSelect + ' ' + @SelectFieldName+ ' from '
+ @tableName + ' where ' + @OrderFieldName + @strTmp + '( '
+ right(@OrderFieldName,len(@OrderFieldName)-charindex( '. ',@OrderFieldName)) + ') from ( ' + 'select top ' + str((@PageIndex-1)*@PageSize+ @BeginIndex)
+ ' ' + @OrderFieldName + ' from ' + @tableName
if @strWhere != ' '
begin
set @strSQL = @strSQL + ' where ' + ' ( ' + @strWhere + ') '
end
SET set @strSQL = @strSQL + @strOrder + ') as tblTmp) '
set @addAnd = ' and '
end
if @strWhere != ' ' -------------如果where子句不为空,加入
begin
set @strSQL = @strSQL + @addAnd + ' ( ' +@strWhere + ') ' + @strOrder
end
else
begin
--------------------------------------加入 order 部分 ----------------------------
set @strSQL = @strSQL + ' ' + @strOrder
end
if ltrim(rtrim(@OrderWhere)) != ' '
begin
if ltrim(rtrim(@OrderWhere)) = ' '
begin
set @OrderWhere= ' order by ' +@OrderWhere
end
else
begin
set @OrderWhere= ', ' +@OrderWhere
end
set @strSQL = @strSQL + @OrderWhere
end
----------------------执行语句---------------------------
end_pro:
begin
--------------------@iRowCount - @BeginIndex-----------得到从@BeginIndex开始的记录数,如果小于0,那么赋值为0
set @iRowCount = @iRowCount - @BeginIndex
if @iRowCount <0
begin
set @iRowCount =0
end
----pageCount------
if(@PageSize <> 0)
set @pageCount= ceiling(convert(float,@iRowCount)/@PageSize)
else
set @pageCount= 1
--------------------
print '@iRowCount= ' + str(@iRowCount)
print 'sql= ' + @strSQL
exec sp_executesql @strSQL
end
GO
[解决办法]
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
CREATE PROCEDURE [GetCustomersDataPage]
@PageIndex INT,
@PageSize INT,
@RecordCount INT OUT,
@PageCount INT OUT
AS
SELECT @RecordCount = COUNT(*) FROM Customers
SET @PageCount = CEILING(@RecordCount * 1.0 / @PageSize)
DECLARE @SQLSTR NVARCHAR(1000)
IF @PageIndex = 0 OR @PageCount <= 1
SET @SQLSTR =N 'SELECT TOP '+STR( @PageSize )+
' CustomerID, CompanyName,Address,Phone FROM Customers ORDER BY CustomerID DESC '
ELSE IF @PageIndex = @PageCount - 1
SET @SQLSTR =N ' SELECT * FROM ( SELECT TOP '+STR( @RecordCount - @PageSize * @PageIndex )+
' CustomerID, CompanyName,Address,Phone FROM Customers ORDER BY CustomerID ASC ) TempTable ORDER BY CustomerID DESC '
ELSE
SET @SQLSTR =N ' SELECT TOP '+STR( @PageSize )+ ' * FROM ( SELECT TOP '+STR( @RecordCount - @PageSize * @PageIndex )+
' CustomerID, CompanyName,Address,Phone FROM Customers ORDER BY CustomerID ASC ) TempTable ORDER BY CustomerID DESC '
EXEC (@SQLSTR)
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
[解决办法]
是用Northwind库,我也是刚从别人那里学的
using System;
using System.Collections;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Web;
using System.Web.SessionState;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.HtmlControls;
using System.Data.SqlClient;
using System.Configuration;
namespace ZZ.AspnetPaging
{
public class WebForm7 : System.Web.UI.Page
{
private int pageCount;
private int recordCount;
protected System.Web.UI.WebControls.LinkButton LBtnFirst;
protected System.Web.UI.WebControls.LinkButton LBtnPrev;
protected System.Web.UI.WebControls.LinkButton LBtnNext;
protected System.Web.UI.WebControls.LinkButton LBtnLast;
protected System.Web.UI.WebControls.Literal LtlPageIndex;
protected System.Web.UI.WebControls.Literal LtlPageCount;
protected System.Web.UI.WebControls.Literal LtlPageSize;
protected System.Web.UI.WebControls.Literal LtlRecordCount;
protected System.Web.UI.WebControls.DataGrid DataGrid1;
private void Page_Load(object sender, System.EventArgs e)
{
if(!Page.IsPostBack)
{
DataGridDataBind();
/*string checkCode = CreateRandomCode(4);
Session[ "CheckCode "]=checkCode;
CreateImage(checkCode); */
}
}
private string CreateRandomCode(int CodeCount)
{
string allChar = "0,1,2,3,4,5,6,7,8,9,A,B,C,D,E,F,G,H,I,J,K,L,M,N,O,P,Q,R,S,T,U,V,W,X,Y,Z ";
string[] allCharArray = allChar.Split(Convert.ToChar( ", "));
string RandomCode = " ";
int temp = -1;
Random rand = new Random();
for (int i=0;i <CodeCount;i++)
{
if (temp != -1)
{
rand = new Random(temp*i*((int) DateTime.Now.Ticks));
}
int t = rand.Next(36);
while (temp == t)
{
t = rand.Next(36);
}
if (t==35)
{
Console.WriteLine(t.ToString() + "| " + i.ToString());
}
temp = t;
RandomCode += allCharArray[t];
}
return RandomCode;
}
private void CreateImage(string checkCode)
{
int iwidth = (int)(checkCode.Length * 11.5);
System.Drawing.Bitmap image = new System.Drawing.Bitmap(iwidth, 20);
Graphics g = Graphics.FromImage(image);
Font f = new System.Drawing.Font( "Arial ", 10, System.Drawing.FontStyle.Bold);
Brush b = new System.Drawing.SolidBrush(Color.White);
//g.FillRectangle(new System.Drawing.SolidBrush(Color.Blue),0,0,image.Width, image.Height);
g.Clear(Color.Blue);
g.DrawString(checkCode, f, b, 3, 3);
Pen blackPen = new Pen(Color.Black, 0);
Random rand = new Random();
for (int i=0;i <5;i++)
{
int y = rand.Next(image.Height);
g.DrawLine(blackPen,0,y,image.Width,y);
}
System.IO.MemoryStream ms = new System.IO.MemoryStream();
image.Save(ms,System.Drawing.Imaging.ImageFormat.Jpeg);
Response.ClearContent();
Response.ContentType = "image/Jpeg ";
Response.BinaryWrite(ms.ToArray());
g.Dispose();
image.Dispose();
}
//绑定数据
private void DataGridDataBind()
{
DataSet ds = GetCustomersData(PageIndex,PageSize,ref recordCount,ref pageCount);
this.DataGrid1.VirtualItemCount = RecordCount;
this.DataGrid1.DataSource = ds;
this.DataGrid1.DataBind();
SetPagingState();
}
#region Web 窗体设计器生成的代码
override protected void OnInit(EventArgs e)
{
InitializeComponent();
base.OnInit(e);
}
private void InitializeComponent()
{
this.LBtnFirst.Click += new System.EventHandler(this.LBtnNavigation_Click);
this.LBtnPrev.Click += new System.EventHandler(this.LBtnNavigation_Click);
this.LBtnNext.Click += new System.EventHandler(this.LBtnNavigation_Click);
this.LBtnLast.Click += new System.EventHandler(this.LBtnNavigation_Click);
this.Load += new System.EventHandler(this.Page_Load);
}
#endregion
private static DataSet GetCustomersData(int pageIndex,int pageSize,ref int recordCount,ref int pageCount)
{
string connString = ConfigurationSettings.AppSettings[ "SQLConnectionString3 "];
SqlConnection conn = new SqlConnection(connString);
SqlCommand comm = new SqlCommand( "GetCustomersDataPage ",conn);
comm.Parameters.Add(new SqlParameter( "@PageIndex ",SqlDbType.Int));
comm.Parameters[0].Value = pageIndex;
comm.Parameters.Add(new SqlParameter( "@PageSize ",SqlDbType.Int));
comm.Parameters[1].Value = pageSize;
comm.Parameters.Add(new SqlParameter( "@RecordCount ",SqlDbType.Int));
comm.Parameters[2].Direction = ParameterDirection.Output;
comm.Parameters.Add(new SqlParameter( "@PageCount ",SqlDbType.Int));
comm.Parameters[3].Direction = ParameterDirection.Output;
comm.CommandType = CommandType.StoredProcedure;
SqlDataAdapter dataAdapter = new SqlDataAdapter(comm);
DataSet ds = new DataSet();
dataAdapter.Fill(ds);
recordCount = (int)comm.Parameters[2].Value;
pageCount = (int)comm.Parameters[3].Value;
return ds;
}
private void LBtnNavigation_Click(object sender, System.EventArgs e)
{
LinkButton btn = (LinkButton)sender;
switch(btn.CommandName)
{
case "First ":
PageIndex = 0;
break;
case "Prev "://if( PageIndex > 0 )
PageIndex = PageIndex - 1;
break;
case "Next "://if( PageIndex < PageCount -1)
PageIndex = PageIndex + 1;
break;
case "Last ":
PageIndex = PageCount - 1;
break;
}
DataGridDataBind();
}
/// <summary>
/// 控制导航按钮或数字的状态
/// </summary>
public void SetPagingState()
{
if( PageCount <= 1 )//( RecordCount <= PageSize )//小于等于一页
{
this.LBtnFirst.Enabled = false;
this.LBtnPrev.Enabled = false;
this.LBtnNext.Enabled = false;
this.LBtnLast.Enabled = false;
}
else //有多页
{
if( PageIndex == 0 )//当前为第一页
{
this.LBtnFirst.Enabled = false;
this.LBtnPrev.Enabled = false;
this.LBtnNext.Enabled = true;
this.LBtnLast.Enabled = true;
}
else if( PageIndex == PageCount - 1 )//当前为最后页
{
this.LBtnFirst.Enabled = true;
this.LBtnPrev.Enabled = true;
this.LBtnNext.Enabled = false;
this.LBtnLast.Enabled = false;
}
else //中间页
{
this.LBtnFirst.Enabled = true;
this.LBtnPrev.Enabled = true;
this.LBtnNext.Enabled = true;
this.LBtnLast.Enabled = true;
}
}
this.LtlPageSize.Text = PageSize.ToString();
this.LtlRecordCount.Text = RecordCount.ToString();
if(RecordCount == 0)
{
this.LtlPageCount.Text = "0 ";
this.LtlPageIndex.Text = "0 ";
}
else
{this.LtlPageCount.Text = PageCount.ToString();
this.LtlPageIndex.Text = (PageIndex + 1).ToString();
}
}
public int PageCount
{
get
{
return this.DataGrid1.PageCount;
}
}
public int PageSize
{
get
{
return this.DataGrid1.PageSize;
}
}
public int PageIndex
{
get
{
return this.DataGrid1.CurrentPageIndex;
}
set
{
this.DataGrid1.CurrentPageIndex = value;
}
}
public int RecordCount
{
get
{
return recordCount;
}
}
}
}
[解决办法]
引用邹老大的
7.2.3 临时表缓存实现的通用分页存储过程.sql
CREATE PROC sp_PageView
@tbname sysname, --要分页显示的表名
@FieldKey nvarchar(1000), --用于定位记录的主键(惟一键)字段,可以是逗号分隔的多个字段
@PageCurrent int=1, --要显示的页码
@PageSize int=10, --每页的大小(记录数)
@FieldShow nvarchar(1000)= ' ', --以逗号分隔的要显示的字段列表,如果不指定,则显示所有字段
@FieldOrder nvarchar(1000)= ' ', --以逗号分隔的排序字段列表,可以指定在字段后面指—ESC/ASC用于指定排序顺序
@Where nvarchar(1000)= ' ', --查询条件
@PageCount int OUTPUT --总页数
AS
SET NOCOUNT ON
--检查对象是否有效
IF OBJECT_ID(@tbname) IS NULL
BEGIN
RAISERROR(N '对象 "%s "不存在 ',1,16,@tbname)
RETURN
END
IF OBJECTPROPERTY(OBJECT_ID(@tbname),N 'IsTable ')=0
AND OBJECTPROPERTY(OBJECT_ID(@tbname),N 'IsView ')=0
AND OBJECTPROPERTY(OBJECT_ID(@tbname),N 'IsTableFunction ')=0
BEGIN
RAISERROR(N ' "%s "不是表、视图或者表值函数 ',1,16,@tbname)
RETURN
END
--分页字段检查
IF ISNULL(@FieldKey,N ' ')= ' '
BEGIN
RAISERROR(N '分页处理需要主键（或者惟一键） ',1,16)
RETURN
END
--其他参数检查及规范
IF ISNULL(@PageCurrent,0) <1 SET @PageCurrent=1
IF ISNULL(@PageSize,0) <1 SET @PageSize=10
IF ISNULL(@FieldShow,N ' ')=N ' ' SET @FieldShow=N '* '
IF ISNULL(@FieldOrder,N ' ')=N ' '
SET @FieldOrder=N ' '
ELSE
SET @FieldOrder=N 'ORDER BY '+LTRIM(@FieldOrder)
IF ISNULL(@Where,N ' ')=N ' '
SET @Where=N ' '
ELSE
SET @Where=N 'WHERE ( '+@Where+N ') '
--如果@PageCount为NULL值,则计算总页数(这样设计可以只在第一次计算总页数,以后调用时,把总页数传回给存储过程,避免再次计算总页数,对于不想计算总页数的处理而言,可以给@PageCount赋值)
IF @PageCount IS NULL
BEGIN
DECLARE @sql nvarchar(4000)
SET @sql=N 'SELECT @PageCount=COUNT(*) '
+N ' FROM '+@tbname
+N ' '+@Where
EXEC sp_executesql @sql,N '@PageCount int OUTPUT ',@PageCount OUTPUT
SET @PageCount=(@PageCount+@PageSize-1)/@PageSize
END
--计算分页显示的TOPN值
DECLARE @TopN varchar(20),@TopN1 varchar(20)
SELECT @TopN=@PageSize,
@TopN1=@PageCurrent*@PageSize
--第一页直接显示
IF @PageCurrent=1
EXEC(N 'SELECT TOP '+@TopN
+N ' '+@FieldShow
+N ' FROM '+@tbname
+N ' '+@Where
+N ' '+@FieldOrder)
ELSE
BEGIN
--生成主键(惟一键)处理条件
DECLARE @Where1 nvarchar(4000),@s nvarchar(1000)
SELECT @Where1=N ' ',@s=@FieldKey
WHILE CHARINDEX(N ', ',@s)> 0
SELECT @s=STUFF(@s,1,CHARINDEX(N ', ',@s),N ' '),
@Where1=@Where1
+N ' AND a. '+LEFT(@s,CHARINDEX(N ', ',@s)-1)
+N '= '+LEFT(@s,CHARINDEX(N ', ',@s)-1)
SELECT @Where1=STUFF(@Where1+N ' AND a. '+@s+N '= '+@s,1,5,N ' '),
@TopN=@TopN1-@PageSize
--执行查询
EXEC(N 'SET ROWCOUNT '+@TopN1
+N ' SELECT '+@FieldKey
+N ' INTO # FROM '+@tbname
+N ' '+@Where
+N ' '+@FieldOrder
+N ' SET ROWCOUNT '+@TopN
+N ' DELETE FROM # '
+N ' SELECT '+@FieldShow
+N ' FROM '+@tbname
+N ' a WHERE EXISTS(SELECT * FROM # WHERE '+@Where1
+N ') '+@FieldOrder)
END