还是这个问题：无法移除＂'tempTablePage1'＂，因为它在系统目录中不存在

haonanernet 2007-05-17 04:25:35
http://community.csdn.net/Expert/topic/5537/5537878.xml?temp=.4858362
...全文
236 9 打赏 收藏 转发到动态 举报
写回复
用AI写文章
9 条回复
切换为时间正序
请发表友善的回复…
发表回复
wgsasd311 2007-05-18
  • 打赏
  • 举报
 回复
---下面是用临时表实现分页
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
wgsasd311 2007-05-18
  • 打赏
  • 举报
 回复
--try
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

--下面是新改的
while (select 1 from sysobjects where id = object_id('tempTablePage1') and type = 'U')>0
WAITFOR DELAY '00:00:00.100' --说明有人正在执行此存储过程，所以需要等别人执行完，才能往下运行

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

--最后要记得删除
drop table tempTablePage1

end
GO

haonanernet 2007-05-18
  • 打赏
  • 举报
 回复
wangtiecheng(不知不为过，不学就是错！) 像上面这么改错在哪呢？
haonanernet 2007-05-18
  • 打赏
  • 举报
 回复



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
shawnwan 2007-05-18
  • 打赏
  • 举报
 回复
支持存储过程
wgsasd311 2007-05-18
  • 打赏
  • 举报
 回复
--纠正下上面临时表的存储过程如下,如还有错误,请楼主告诉我,先谢了:)
---下面是用临时表实现分页
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 SP_EXECUTESQL @sql,N'@nCount int
,@PageSize int
,@nTotalPage int
,@PageIndex int ',
@nCount
,@PageSize
,@nTotalPage
,@PageIndex

end
GO
OracleRoob 2007-05-17
  • 打赏
  • 举报
 回复
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动态创建的临时表，在外部无法访问。
jyxhz 2007-05-17
  • 打赏
  • 举报
 回复
用事务把整个存储过程包住
OracleRoob 2007-05-17
  • 打赏
  • 举报
 回复
像这种业务，最好使用局部临时表，如：#临时表名

这样就不会冲突了。

但是需要注意：如果要在生成临时表后再使用，不能在动态SQL中创建，需要先创建好表结构。
springboot(酒店管理系统)
开发语言：Java JDK版本：JDK1.8（或11） 服务器：tomcat 数据库：mysql 5.6/5.7（或8.0） 数据库工具：Navicat 开发软件：idea 依赖管理包：Maven 代码+数据库保证完整可用，可提供远程调试并指导运行服务（额外付费）～ 如果对系统的中的某些部分感到不合适可提供修改服务，比如题目、界面、功能等等... 声明： 1.项目已经调试过，完美运行 2.需要远程帮忙部署项目，需要额外付费 3.本项目有演示视频，如果需要观看，请联系我 4.调试过程中可帮忙安装IDEA，eclipse，MySQL，JDK，Tomcat等软件 重点： 需要其他Java源码联系我，更多源码任你选，你想要的源码我都有！ 需要加v19306446185
BP神经网络matlab实例.doc
数学模型算法
设计.zip
设计.zip
基于 Spring Cloud 组件构建的分布式服务架构
Java SSM项目是一种使用Java语言和SSM框架（Spring + Spring MVC + MyBatis）开发的Web应用程序。SSM是一种常用的Java开发框架组合，它结合了Spring框架、Spring MVC框架和MyBatis框架的优点，能够快速构建可靠、高效的企业级应用。 1. Spring框架：Spring是一个轻量级的Java开发框架，提供了丰富的功能和模块，用于开发企业级应用。它包括IoC（Inverse of Control，控制反转）容器、AOP（Aspect-Oriented Programming，面向切面编程）等特性，可以简化开发过程、提高代码的可维护性和可测试性。 2. Spring MVC框架：Spring MVC是基于Spring框架的Web框架，用于开发Web应用程序。它采用MVC（Model-View-Controller，模型-视图-控制器）的架构模式，将应用程序分为模型层、视图层和控制器层，提供了处理请求、渲染视图和管理流程的功能。 3. MyBatis框架：MyBatis是一个持久层框架，用于与数据库进行交互。它提供了一种将数据库操作与Java对象映射起来的方式，避免了手动编写繁琐的SQL语句，并提供了事务管理和缓存等功能，简化了数据库访问的过程
【微信小程序毕业设计】书店系统开发项目(源码+演示视频+说明).rar
【微信小程序毕业设计】书店系统开发项目(源码+演示视频+说明).rar 【项目技术】 微信小程序开发工具+java后端+mysql 【演示视频-编号：246】 https://pan.quark.cn/s/cb634e7c02b5 【实现功能】 用户信息管理，图书信息管理，图书类型管理，图书留言管理，论坛信息管理等
MS-SQL Server

34,590

社区成员

254,589

社区内容

发帖
与我相关
我的任务
社区描述
MS-SQL Server相关内容讨论专区
社区管理员
  • 基础类社区
  • 二月十六
  • 卖水果的net
加入社区
  • 近7日
  • 近30日
  • 至今

加载中

查看更多榜单
社区公告
暂无公告

试试用AI创作助手写篇文章吧

+ 用AI写文章