分页的存储过程的问题
下面是我的一段(ASP)用于分页的存储过程:
为什么当有记录集时可以正常执行,为什当没有记录集时有如下的提示:
Microsoft OLE DB Provider for ODBC Drivers 错误 '80040e14'
[Microsoft][ODBC SQL Server Driver][SQL Server]第 2 行: '-' 附近有语法错误。
/AdminControl/RelationsSetting.asp,行 133
我的调用该存储过程的ASP语句:
Set objRs=objconn.execute("sp_search_breakpage 'LucyDB_Connection','"&QueryWord&"','"&Line&"','"&CInt(page)&"'")
存储过程:
CREATE PROCEDURE sp_search_breakpage
@tablename varchar(50),
@title varchar(250),
@pagesize int,
@page int
AS
if @tablename='LucyDB_Connection'
begin
declare @ks int
declare @str varchar(200)
set @ks=@pagesize*(@page-1)
if not exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[temp_table91]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
begin
select * into temp_table91 from LucyDB_Connection where
LucyDB_Connection_ConnectionName like '%'+@title+'%' order
by LucyDB_Connection_CreateTime desc
set rowcount @pagesize
set @str='select * from temp_table91 where LucyDB_Connection_ID not in
(select top '+str(@ks)+' LucyDB_Connection_ID from temp_table91)'
execute(@str)
drop table temp_table91
end
end
GO