无法移除＂'tempTablePage1'＂，因为它在系统目录中不存在

[haonanernet]

CREATE PROCEDURE spSelectListDynamicPaged2
(
@SelectStatement nvarchar(4000),
@FromStatement nvarchar(2000),
@WhereStatement nvarchar(4000),
@OrderByExpression nvarchar(500),
--add @AscOrDesc
@AscOrDesc nvarchar(10),
@RecordCount int,
@PageSize int,
@PageIndex int,
@DoCount bit
)

AS

SET NOCOUNT ON

IF(@DoCount=1)
--if do count, return the count simply

EXEC('SELECT count(*) FROM '+@FromStatement+' WHERE 1=1 '+@WhereStatement)
ELSE
BEGIN

declare @nCount as int
declare @nTotalPage As int
declare @sSelectCopy As nvarchar(2000)
declare @TempTable As nvarchar(100)

if isnull(@WhereStatement,'') = ''
begin
set @WhereStatement = '1=1'
end
else
begin
set @WhereStatement = right(ltrim(@WhereStatement),len(ltrim(@WhereStatement))-3)
end


if isnull(@OrderByExpression,'') = ''
begin
set @OrderByExpression = '1'
end

if exists (select 1 from sysobjects where id = object_id('tempTablePage1') and type = 'U')
begin
drop table tempTablePage1
end

set @SelectStatement = 'select top 100000000 ' + @SelectStatement


exec ('select a.* , identity(int,1,1) as NumberIndex into tempTablePage1 from (' + @SelectStatement + ' from ' + @FromStatement + ' where ' + @WhereStatement + ' order by ' + @OrderByExpression + ' ' + @AscOrDesc +') as a')

set @nCount = (select max(NumberIndex) from tempTablePage1)
if @nCount % @PageSize > 0
begin
set @nTotalPage = @nCount / @PageSize + 1
end
else
begin
set @nTotalPage = @nCount / @PageSize
end

if @PageIndex <= 0
begin
set @PageIndex = 1
end
else if @PageIndex > @nTotalPage
begin
set @PageIndex = @nTotalPage
end


if @PageIndex < @nTotalPage
begin
select * from tempTablePage1 where NumberIndex between (@PageIndex - 1)*@PageSize + 1 and @PageIndex*@PageSize
end
else if @PageIndex = @nTotalPage
begin
select * from tempTablePage1 where NumberIndex between (@PageIndex-1)*@PageSize + 1 and @nCount
end

if exists (select 1 from sysobjects where id = object_id('tempTablePage1') and type = 'u')
begin
drop table tempTablePage1
end

end
GO
－－－－－－－－－－－－－－－－－－－－－－－－－－－
用了上面的这个分页存储过程，发现偶尔出现
无法移除＂'tempTablePage1'＂，因为它在系统目录中不存在
这种错误．发生的频率十分少．但是不知道是什么bug?

[haonanernet]

if exists (select 1 from sysobjects where id = object_id('tempTablePage1') and type = 'U')
begin
drop table tempTablePage1
end
-----------------------
这么写会有什么错误呢？

[OracleRoob]

可能并发操作太频繁，导致判断的时候有这个表，而删除的时候已经不存在了。

[haonanernet]

有办法处理吗？

[OracleRoob]

像这种业务，最好使用局部临时表，如：#临时表名

这样就不会冲突了。

但是需要注意：如果要在生成临时表后再使用，不能在动态SQL中创建，需要先创建好表结构。

[haonanernet]

wangtiecheng(不知不为过，不学就是错！)
－－－－－－－－－－
不懂，能讲的详细一点吗？
就像我那个存储过程该怎么修改呢，谢谢

[wgsasd311]

if exists (select 1 from sysobjects where id = object_id('tempTablePage1') and type = 'U')=====改为下面语句试试：
if exists (select * from dbo.sysobjects where id = object_id(N'[tempTablePage1]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)

[toddzst]

up

[Andy-W]

楼主可以使用@@ERROR来捕获错误发生在哪一个DROP 过程。
象楼主的这样，建议把tempTablePage1改成使用临时表#tempTablePage1，就如wangtiecheng(不知不为过，不学就是错！) 说的方法解决。

[ojuju10]

楼主先建一个临时表

把select * into 临时表 from table 改为：用insert into 临时表 select * from table

在drop table 临时表改为：truncate table 临时表

[haonanernet]

我的临时表是动态生成的，怎么创建？

[Tongls]

1.临时表不用删除，只要断了连接就自动删除。。

2.创建临时表可能使Create Table ，也可以使用Select Into

[haonanernet]

wgsasd311(自强不息)
-----------
这样改有什么区别呢？

[ojuju10]

临时表是在系统数据库tempdb的sysobjects表中，当断开本次链接时，自动从tempdb库中的sysobjects表中删除，它又不在当前所创建的数据库中

[ojuju10]

use pubs
go
create table #a (a int)

use tempdb
go
select top 1 name from sysobjects
where type='u'
order by crdate desc
name
--------------------------------------------------------------------------------------------------------------------------------
#a__________________________________________________________________________________________________________________000000000019

（所影响的行数为 1 行）

----表名都变了！

[OracleRoob]

exec ('select a.* , identity(int,1,1) as NumberIndex into tempTablePage1 from (' + @SelectStatement + ' from ' + @FromStatement + ' where ' + @WhereStatement + ' order by ' + @OrderByExpression + ' ' + @AscOrDesc +') as a')

这里面的tempTablePage1 替换为临时表#tempTablePage1 ，并把之后的所有SQL都写到这个Exec 的动态脚本中。因为在Exec动态创建的临时表，在外部无法访问。

[haonanernet]

针对我这个应该怎么改呢？

[haonanernet]

CREATE PROCEDURE spSelectListDynamicPaged2
(
@SelectStatement nvarchar(4000),
@FromStatement nvarchar(2000),
@WhereStatement nvarchar(4000),
@OrderByExpression nvarchar(500),
--add @AscOrDesc
@AscOrDesc nvarchar(10),
@RecordCount int,
@PageSize int,
@PageIndex int,
@DoCount bit
)

AS

SET NOCOUNT ON

IF(@DoCount=1)
--if do count, return the count simply

EXEC('SELECT count(*) FROM '+@FromStatement+' WHERE 1=1 '+@WhereStatement)
ELSE
BEGIN
declare @nCount as int
declare @nTotalPage As int
declare @sSelectCopy As nvarchar(2000)
declare @TempTable As nvarchar(100)

if isnull(@WhereStatement,'') = ''
begin
set @WhereStatement = '1=1'
end
else
begin
set @WhereStatement = right(ltrim(@WhereStatement),len(ltrim(@WhereStatement))-3)
end


if isnull(@OrderByExpression,'') = ''
begin
set @OrderByExpression = '1'
end

if exists (select 1 from sysobjects where id = object_id('#tempTablePage1') and type = 'U')
begin
drop table #tempTablePage1
end

set @SelectStatement = 'select top 100000000 ' + @SelectStatement
--创建一个临时表
exec (N'select a.* , identity(int,1,1) as NumberIndex into #tempTablePage1 from (' + @SelectStatement + N' from ' + @FromStatement + N' where ' + @WhereStatement + N' order by ' + @OrderByExpression + ' ' + @AscOrDesc +N') as a'
+N' set '+@nCount+N' = (select max(NumberIndex) from #tempTablePage1) '
+N' if '+@nCount+N' % '+@PageSize+N' > 0 '
+N' begin '
+N' set '+@nTotalPage+N' = '+@nCount+N' / '+@PageSize+N' + 1 '
+N' end '
+N' else '
+N' begin '
+N' set '+@nTotalPage+N' = '+@nCount+N' / '+@PageSize
+N' end '

+N' if '+@PageIndex+N' <= 0 '
+N' begin '
+N' set '+@PageIndex+N' = 1 '
+N' end '
+N' else if '+@PageIndex+N' > '+@nTotalPage
+N' begin '
+N' set '+@PageIndex+N' = '+@nTotalPage
+N' end '


+N' if '+@PageIndex+N' < '+@nTotalPage
+N' begin '
+N' select * from #tempTablePage1 where NumberIndex between ('+@PageIndex+N' - 1)*'+@PageSize+N' + 1 and '+@PageIndex+N'*'+@PageSize
+N' end '
+N' else if '+@PageIndex+N' = '+@nTotalPage
+N' begin '
+N' select * from #tempTablePage1 where NumberIndex between ('+@PageIndex+N'-1)*'+@PageSize+N' + 1 and '+@nCount
+N' end '
)
if exists (select 1 from sysobjects where id = object_id('#tempTablePage1') and type = 'u')
begin
drop table #tempTablePage1
end

end
GO
-----------------------------------------------
像上面这么改错误在哪呢？