优秀的分页存储过程 -- asp#

CREATE PROCEDURE GetRecordFromPage
@tblName varchar(255), -- 表名
@fldName varchar(255), -- 字段名
@PageSize int = 10, -- 页尺寸
@PageIndex int = 1, -- 页码
@IsCount bit = 0, -- 返回记录总数, 非 0 值则返回
@OrderType bit = 0, -- 设置排序类型, 非 0 值则降序
@strWhere varchar(1000) = '' -- 查询条件 (注意: 不要加 where)
AS

declare @strSQL varchar(2000) -- 主语句
declare @strTmp varchar(1000) -- 临时变量
declare @strOrder varchar(1000) -- 排序类型

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"
end

set @strSQL = "select top " + str(@PageSize) + " * from ["
+ @tblName + "] where [" + @fldName + "]" + @strTmp + "(["
+ @fldName + "]) from (select top " + str((@PageIndex-1)*@PageSize) + " ["
+ @fldName + "] from [" + @tblName + "]" + @strOrder + ") as tblTmp)"
+ @strOrder

if @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 + ") " + @strOrder

if @PageIndex = 1
begin
set @strTmp = ""
if @strWhere != ''
set @strTmp = " where (" + @strWhere + ")"

set @strSQL = "select top " + str(@PageSize) + " * from ["
+ @tblName + "]" + @strTmp + " " + @strOrder
end

if @IsCount != 0
set @strSQL = "select count(*) as Total from [" + @tblName + "]"

exec (@strSQL)
GO

要注意看,修改后的存储过程在使用@strWhere时,都在其前后加上了(),这样,就防止嵌套的()出现错误

下面的代码是引用该存储过程的一个范例

SqlConnection MyConnection=new SqlConnection(ConfigurationSettings.AppSettings["dsn"]);
DataSet MyDataSet=new DataSet();
string strKeyword=Keyword.Text.Trim().Replace("/'","/'/'");
string strSalesId=Sales.SelectedItem.Value;
int RecordCount=CalcRecordCount();
RecordNumber.Text=RecordCount.ToString();
LblRecordNumber.Text=RecordCount.ToString();
string strExpress="Id<>0";
if (strKeyword!="")
strExpress=strExpress+" and (companyenname like '%"+strKeyword+"%' orcompanychname like '%"+strKeyword+"%' or Companyshortname like'%"+strKeyword+"%' or web like '%"+strKeyword+"%' or mainproduct like'%"+strKeyword+"%' or phone like '%"+strKeyword+"%' or memo like'%"+strKeyword+"%' or address like '%"+strKeyword+"%' or linkmanphonelike '%"+strKeyword+"%')";
if (strSalesId!="")
strExpress=strExpress+" and salesid="+strSalesId;
SqlCommand MyCommand=new SqlCommand();
MyCommand.Connection=MyConnection;
MyCommand.CommandText="GetRecordFromPage";
MyCommand.CommandType=CommandType.StoredProcedure;
MyCommand.Parameters.Add("@tblName","customerview");
MyCommand.Parameters.Add("@fldName","id");
MyCommand.Parameters.Add("@strWhere",strExpress);
MyCommand.Parameters.Add("@PageSize",Int32.Parse(CustomerList.PageSize.ToString()));
MyCommand.Parameters.Add("@PageIndex",Int32.Parse(ViewState["PageIndex"].ToString())+1);
SqlDataReader MyReader;
MyConnection.Open();
MyReader=MyCommand.ExecuteReader();
CustomerList.VirtualItemCount=RecordCount;
CustomerList.DataSource=MyReader;
CustomerList.DataKeyField="id";
CustomerList.DataBind();
MyReader.Close();
MyConnection.Close();

在这里,要注意的是存储过程使用的PAGEINDEX变量是从1开始

就算条件再复杂,也能搞定

CREATE procedure page
(@pagesize int,
@pageindex int,
@docount bit)
as
set nocount on
if(@docount=1)
select count(UserID) from Users
else
begin
declare @PageLowerBound int

declare @PageUpperBound int
set @PageLowerBound=(@pageindex-1)*@pagesize
set @PageUpperBound=@PageLowerBound+@pagesize
create table #pageindex(id int identity(1,1) not null,nid int)
set rowcount @PageUpperBound
insert into #pageindex(nid)
select UserID from Users order by UserID desc
select O.UserName,O.Uedate,UserID
from Users O,#pageindex p
where O.UserID=p.nid and p.id&gt;@PageLowerBound and p.id&lt;=@PageUpperBound order by p.id
end
GO