还是这个问题：无法移除＂'tempTablePage1'＂，因为它在系统目录中不存在
http://community.csdn.net/Expert/topic/5537/5537878.xml?temp=.4858362

------解决方案--------------------
像这种业务，最好使用局部临时表，如：#临时表名

这样就不会冲突了。

但是需要注意：如果要在生成临时表后再使用，不能在动态SQL中创建，需要先创建好表结构。
------解决方案--------------------
用事务把整个存储过程包住
------解决方案--------------------
---下面是用临时表实现分页
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


set @SelectStatement = 'select top 100000000 ' + @SelectStatement
--新改的
declare @sql nvarchar(4000)
set @sql=N 'select a.* , identity(int,1,1) as NumberIndex into tempTablePage1 from ( ' + @SelectStatement + ' from ' + @FromStatement + ' where ' + @WhereStatement + ' order by ' + @OrderByExpression + ' ' + @AscOrDesc + ') as a '
+N ' set @nCount = (select max(NumberIndex) from tempTablePage1) '
+N ' if @nCount % @PageSize > 0 '
+N ' set @nTotalPage = @nCount / @PageSize + 1 '
+N ' else '
+N ' set @nTotalPage = @nCount / @PageSize '

+N ' if @PageIndex <= 0 '
+N ' set @PageIndex = 1 '
+N ' else if @PageIndex > @nTotalPage '
+N ' set @PageIndex = @nTotalPage '
+N ' if @PageIndex < @nTotalPage '
+N ' select * from tempTablePage1 where NumberIndex between (@PageIndex - 1)*@PageSize + 1 and @PageIndex*@PageSize '
+N ' else if @PageIndex = @nTotalPage '
+N ' select * from tempTablePage1 where NumberIndex between (@PageIndex-1)*@PageSize + 1 and @nCount '
EXEC(@sql)

end
GO