存储过程代码:
ALTER PROCEDURE sp_Search @tblName varchar(255), -- 表名
@strGetFields varchar(1000) = '* ', -- 需要返回的列
@fldName varchar(255)= ' ', -- 排序的字段名
@PageSize int = 25, -- 页尺寸
@PageIndex int = 1, -- 页码
@doCount bit = 0, -- 返回记录总数, 非 0 值则返回
@OrderType bit = 1, -- 设置排序类型, 非 0 值则降序
@strWhere varchar(1500) = ' ' -- 查询条件 (注意: 不要加 where) AS declare @strSQL varchar(5000) -- 主语句
declare @strTmp varchar(110) -- 临时变量
declare @strOrder varchar(400) -- 排序类型 if @doCount != 0
begin
if @strWhere != ' '
set @strSQL = 'select count(*) as Total from [ ' + @tblName + '] where '+@strWhere
else
set @strSQL = 'select count(*) as Total from [ ' + @tblName + '] '
end else
begin
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 if @PageIndex = 1
begin
if @strWhere != ' '
set @strSQL = 'select top ' + str(@PageSize) + ' '+@strGetFields+ ' from [ ' + @tblName + '] where ' + @strWhere + ' ' + @strOrder
else
set @strSQL = 'select top ' + str(@PageSize) + ' '+@strGetFields+ ' from [ '+ @tblName + '] '+ @strOrder
end else
begin
set @strSQL = 'select top ' + str(@PageSize) + ' '+@strGetFields+ ' 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) + ' '+@strGetFields+ ' from [ '
+ @tblName + '] where [ ' + @fldName + '] ' + @strTmp + '([ '
+ @fldName + ']) from (select top ' + str((@PageIndex-1)*@PageSize) + ' [ '
+ @fldName + '] from [ ' + @tblName + '] where ' + @strWhere + ' '
+ @strOrder + ') as tblTmp) and ' + @strWhere + ' ' + @strOrder
end end exec (@strSQL) asp文件调用代码:
<p><% Dim CmdSP,rs
set CmdSP = Server.CreateObject("ADODB.Command")
CmdSP.ActiveConnection = cnn
CmdSP.CommandText = "sp_search"
CmdSP.CommandType = adCmdStoredProc
CmdSP.Prepared = true
Set rs = cnn.Execute("sp_Search 'v_datasheets', 'Part,Name', 'part',10," & request.querystring("p") & ",0,1,'brand=" & rs("ID") & "' ")
While Not rs.EOF
% <br><%=rs("name")%> <br>
<%rs1.MoveNext
Wend%></p>现在要获取rs的记录总数进行分页,请教该怎样写呢。
ALTER PROCEDURE sp_Search @tblName varchar(255), -- 表名
@strGetFields varchar(1000) = '* ', -- 需要返回的列
@fldName varchar(255)= ' ', -- 排序的字段名
@PageSize int = 25, -- 页尺寸
@PageIndex int = 1, -- 页码
@doCount bit = 0, -- 返回记录总数, 非 0 值则返回
@OrderType bit = 1, -- 设置排序类型, 非 0 值则降序
@strWhere varchar(1500) = ' ' -- 查询条件 (注意: 不要加 where) AS declare @strSQL varchar(5000) -- 主语句
declare @strTmp varchar(110) -- 临时变量
declare @strOrder varchar(400) -- 排序类型 if @doCount != 0
begin
if @strWhere != ' '
set @strSQL = 'select count(*) as Total from [ ' + @tblName + '] where '+@strWhere
else
set @strSQL = 'select count(*) as Total from [ ' + @tblName + '] '
end else
begin
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 if @PageIndex = 1
begin
if @strWhere != ' '
set @strSQL = 'select top ' + str(@PageSize) + ' '+@strGetFields+ ' from [ ' + @tblName + '] where ' + @strWhere + ' ' + @strOrder
else
set @strSQL = 'select top ' + str(@PageSize) + ' '+@strGetFields+ ' from [ '+ @tblName + '] '+ @strOrder
end else
begin
set @strSQL = 'select top ' + str(@PageSize) + ' '+@strGetFields+ ' 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) + ' '+@strGetFields+ ' from [ '
+ @tblName + '] where [ ' + @fldName + '] ' + @strTmp + '([ '
+ @fldName + ']) from (select top ' + str((@PageIndex-1)*@PageSize) + ' [ '
+ @fldName + '] from [ ' + @tblName + '] where ' + @strWhere + ' '
+ @strOrder + ') as tblTmp) and ' + @strWhere + ' ' + @strOrder
end end exec (@strSQL) asp文件调用代码:
<p><% Dim CmdSP,rs
set CmdSP = Server.CreateObject("ADODB.Command")
CmdSP.ActiveConnection = cnn
CmdSP.CommandText = "sp_search"
CmdSP.CommandType = adCmdStoredProc
CmdSP.Prepared = true
Set rs = cnn.Execute("sp_Search 'v_datasheets', 'Part,Name', 'part',10," & request.querystring("p") & ",0,1,'brand=" & rs("ID") & "' ")
While Not rs.EOF
% <br><%=rs("name")%> <br>
<%rs1.MoveNext
Wend%></p>现在要获取rs的记录总数进行分页,请教该怎样写呢。
if @doCount != 0
begin
if @strWhere != ' '
set @strSQL = 'select count(*) as Total from [ ' + @tblName + '] where '+@strWhere
else
set @strSQL = 'select count(*) as Total from [ ' + @tblName + '] '
end 这里<%=rs("Total")% >就可以了啊!
或者select count(*) ..
如果在动态语句中
可以在外部用@@ROWCOUNT获得
也可以, 在exec语句内部用变量获得count数,可以是用@@ROWCOUNT,也可以是select count,然后用sp_executesql取得
如果 count值是以行集呈现的,那么用 ado.recordset.nextrecordset方法获取即可.
比如
create proc mysp
as
select * from tb
select @@rowcount
set rs=conn.execute("mysp")
dim rs1:set rs1=server.createobject("adodb.recordset")
set rs1=rs.nextrecordset
@doCount bit = 0, -- 返回记录总数, 非 0 值则返回 我把这里改为1了,但<%=rs("Total")% >这样取不到记录总数提示:列名 'Total' 无效。
<%=rs(0)% >输出是strFields中指定的第一列行一行的值
EXEC('SELECT TOP x filedslist FROM ... ')
而不是执行
EXEC('SELECT COUNT(*) FROM .... ; SELECT TOP x fieldslist FROM ...')检查 @doCount 的参数值, 使存储过程执行 SELECT COUNT(*) ... 和 SELECT TOP x Fieldslist From ..
两个操作set rs=cmdsp.execute(....)
response.write(rs(0))
即可得到count值然后再用 rs.nextrecordset 方法得到 select top 所产生的行集.
@strGetFields varchar(1000) = '* ', -- 需要返回的列
@fldName varchar(255)= ' ', -- 排序的字段名
@PageSize int = 25, -- 页尺寸
@PageIndex int = 1, -- 页码
@doCount bit = 0, -- 返回记录总数, 非 0 值则返回
@OrderType bit = 1, -- 设置排序类型, 非 0 值则降序
@strWhere varchar(1500) = ' ' -- 查询条件 (注意: 不要加 where) AS declare @strSQL varchar(5000) -- 主语句
declare @strTmp varchar(110) -- 临时变量
declare @strOrder varchar(400) -- 排序类型 if @doCount != 0
begin
if @strWhere != ' '
set @strSQL = 'select count(*) as Total from ['+@tblName+'] where '+@strWhere
else
set @strSQL = 'select count(*) as Total from ['+@tblName+'] '
end else
begin
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 if @PageIndex = 1
begin
if @strWhere != ' '
set @strSQL = 'select top ' + str(@PageSize) + ' '+@strGetFields+ ' from [ ' + @tblName + '] where ' + @strWhere + ' ' + @strOrder
else
set @strSQL = 'select top ' + str(@PageSize) + ' '+@strGetFields+ ' from [ '+ @tblName + '] '+ @strOrder
end else
begin
set @strSQL = 'select top ' + str(@PageSize) + ' '+@strGetFields+ ' 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) + ' '+@strGetFields+ ' from [ '
+ @tblName + '] where [ ' + @fldName + '] ' + @strTmp + '([ '
+ @fldName + ']) from (select top ' + str((@PageIndex-1)*@PageSize) + ' [ '
+ @fldName + '] from [ ' + @tblName + '] where ' + @strWhere + ' '
+ @strOrder + ') as tblTmp) and ' + @strWhere + ' ' + @strOrder
end end EXEC (@strSQL)
GO
EXEC DBO.sp_Search 'TestTwo','*','id',20,1, 1,1,''DROP PROC sp_SearchTotal
-----------
310在这里的时候,这里以前在['+@tblName+']周围的空格引起了错误,要仔细检查一下!用是可以用的
if @strWhere != ' '
set @strSQL = 'select count(*) as Total from ['+@tblName+'] where '+@strWhere
else
set @strSQL = 'select count(*) as Total from ['+@tblName+'] '
EXEC DBO.sp_Search 'sysobjects', '* ', 'id ',20,1, 1,1, ' '
来测试一下
这样执行很正常,但如果改成这样:
Set rs = cnn.Execute("sp_Search 'v_datasheets ', 'Part,Name ', 'part ',10," & request.querystring("p") & ",1,1, 'brand=" & rs("ID") & " ' ")
执行时出错ADODB.Recordset 错误 '800a0cc1' 在对应所需名称或序数的集合中,未找到项目。