2024年2月20日发(作者：)
ASP+SQL Server带条件查询的分页存储过程及其ASP调用实例
(该例子已经在环境IIS+ASP+SQLServer调试过可用，供Web初学者直接试用，如有问题请及时留言指正)
1。准备Server SQL 建立数据库表Diary
数据库表Diary建立代码：
if exists (select * from ects where id = object_id(N'[dbo].[xDiary]') and
OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[xDiary]
GO
CREATE TABLE [dbo].[xDiary] (
[DiaryID] [int] IDENTITY (1, 1) NOT NULL ,
[DiaryDate] [smalldatetime] NOT NULL ,
[DiaryName] [nvarchar] (50) COLLATE Chinese_PRC_CI_AS NOT NULL ,
[DiaryInfo] [ntext] COLLATE Chinese_PRC_CI_AS NOT NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
2.创建记录总集的存储过程cn_RecordCount
存储过程Cn_RecordCount代码：
CREATE PROCEDURE [dbo].[cn_RecordCount] --返回记录总集的存储过程
@TableName nvarchar(100), --数据库表名
@strWhere varchar(500), --筛选条件
@count int output --记录集总数
AS
declare @sqlStr nvarchar(1000)
if @strWhere!=''
set @sqlStr=N'select @count=count(*) from ' +@TableName+' where 1=1
'+@strWhere
else
set @sqlStr=N'select @count=count(*) from '+@TableName
exec sp_executesql @sqlstr,N'@count int output',@count output
GO
3.分页的存储过程cn_PageView
分页的存储过程Cn_PageView：
CREATE PROCEDURE [dbo].[cn_PageView]
@tablename varchar(200),
@strGetFields varchar(200),
@PageIndex int,
@PageSize int,
@strWhere varchar(100),
@strOrder varchar(100),
@intOrder bit
AS
begin
declare @strSql varchar(500)
declare @strTemp varchar(100)
declare @strOrders varchar(50)
declare @table varchar(70)
if @intOrder = 0
begin
set @strTemp='>(select max'
set @strOrders=' order by '+@strOrder+' asc '
end
else
begin
set @strTemp='<(select min'
set @strOrders=' order by '+@strOrder+' desc '
end
if @PageIndex=1
begin
if @strWhere=''
begin
set @strSql='select top '+str(@PageSize)+@strGetFields+' from
'+@tablename+' ' +@strOrders
end
else
begin
set @strSql='select top '+str(@PageSize)+@strGetFields+' from
'+@tablename+' where '+@strWhere+' '+@strOrders
end
end
else
begin
set @strSql = 'select top'+str(@pageSize)+' '+@strGetFields+' from
'+@tablename+' where '+@strOrder+' '+@strTemp+' ('+@strOrder+')'
+' from (select top '+str((@pageIndex-1)*@pageSize)+' '+@strGetFields+' from
'+@tablename+ ' '+@strOrders+ ') as tempTable ) '+@strOrders
--set @strSql='select top '+str(@PageSize)+' '+@strGetFields+' from
'+@tablename+' where '+@strOrder+' '+@strTemp+' (' +@strOrder+') '
--+' from (select top '+str((@PageIndex-1)*@PageSize)+' '+@strGetFields+' from
'+@tablename+' '+@strOrders+') as TempTable) '+@strOrders
if @strWhere!=' '
begin
set @strSql= 'select top '+str(@pageSize)+ ' '+@strGetFields+' from
'+@tablename+ ' where '+@strOrder+ ' '+@strTemp+' ('+@strOrder+') '
+' from(select top '+str((@pageIndex-1)*@pageSize)+' '+@strGetFields+' from
'+@tablename+' where '+@strWhere+' ' +@strOrders+') as tempTable) and
'+@strWhere+' '+@strOrders
end
end
exec(@strSql)
end
GO
页面调用存储分页实例
ASP页面调用存储分页代码：
<%Self=Variables("Script_Name")
DataSource="你的服务器名"
Catalog="你的数据库名"
UID="sa"
PWD=""
Connstr="Provider=SQLOLEDB;Data Source="&DataSource&";Initial
CataLog="&Catalog&";UID="&UID&";PWD="&PWD&";"
set rs=Object("set")
set Conn=Object("tion")
ConnStr%>
<%function MyLabel(En,CH)%> title="<%=En%>"><%=CH%>
| <% dim Kind,Key,TableName,strGetFields,PageNo,PageSize,strWhere,strWhere2,strOrder TableName="xDiary" strGetFields=" DiaryID,DiaryName,DiaryInfo,DiaryDate " strOrder="DiaryDate" PageNo=int(request("PageNo")) PageSize=10 Kind=Request("Kind") Key=Request("Key") if Key<>"" then strWhere=" and "&Kind&" like '%"&Key&"%' " strWhere2=" "&Kind&" like ''%"&Key&"%'' " else strWhere="" strWhere2="" end if Set Comm=Object("d") Set Connection=Conn dText="cn_RecordCount" dType=4 ed=true
Parameter("@TableName",200,1,500,TableName)
Parameter("@strWhere",200,1,500,strWhere) Parameter("@count",3,2) e RecordCount=ters("@count").value set Comm=nothing if RecordCount mod PageSize=0 then PageCount=RecordCountPageSize else PageCount=RecordCountPageSize+1 end if if PageNo="" or PageNo<=0 then PageNo=1 end if sql="exec cn_PageView '"&TableName&"','"&strGetFields&"',"&PageNo&","&PageSize&",'"&strWhere2&"','"&strOrder&"',1"
set rs=object("set") 'MySQL(sql) %>
<%else " No Data！ | end if%>
|
发布评论