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

haonanernet 2007-05-17 04:25:35
http://community.csdn.net/Expert/topic/5537/5537878.xml?temp=.4858362
...全文
248 9 打赏 收藏 转发到动态 举报
AI 作业
写回复
用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中创建，需要先创建好表结构。
TM650 2.4.56.1-2025.pdf
TM650 2.4.56.1-2025.pdf
丰富的页面结构v2.pptx
丰富的页面结构v2.pptx
智能汽车基于AUTOSAR的软件定义汽车架构设计：HPC与区域控制器集成及OTA更新系统实现
内容概要：本文介绍了软件定义汽车（SDV）的最佳实践案例，重点围绕基于Vector技术的电子电气（E/E）架构设计与实现。文档展示了高算力计算平台（HPC）、区域控制器（Zone ECU）和车载网络（如CAN、Ethernet）的系统架构布局，并结合AUTOSAR操作系统（Classic/Adaptive）、虚拟化（Hypervisor）和SOA服务设计，构建现代化车载系统。通过vCANdrive平台演示了从开发、测试（SIL/HIL）、到OTA升级的全流程，涵盖传感器、执行器、应用层软件及云端协同的集成方案。同时展示了硬件原型（如树莓派、Triboard）和MICROSAR系列工具链在实际项目中的应用。; 适合人群：从事汽车电子系统开发、车载软件架构设计以及智能网联汽车研发的工程师和技术管理人员，具备一定的嵌入式系统或AUTOSAR基础者更佳。; 使用场景及目标：①理解软件定义汽车的整体架构设计方法；②掌握基于Vector工具链的HPC与区域控制器集成方案；③实现OTA更新、SIL/HIL测试、ETH-CAN通信转换等关键技术验证；④支持智能驾驶（ADAS）与智能座舱（IVI）系统的快速原型开发。; 阅读建议：建议结合Vector相关工具（如PREEvision、CANoe4SW、MICROSAR）进行实践操作，重点关注系统分层设计、通信机制与软件更新流程，同时可参考文档中的硬件连接示意图与信号映射关系进行仿真与实车验证。
afasdfasdfasdfononoi
sadflnksaiower
drawio网络拓扑图绘制工具_网络拓扑图绘制工具_用于绘制和编辑网络拓扑图的在线工具_帮助网络工程师和系统管理员可视化网络结构_支持多种网络设备图标和连接线_提供拖放式编辑和实.zip
drawio网络拓扑图绘制工具_网络拓扑图绘制工具_用于绘制和编辑网络拓扑图的在线工具_帮助网络工程师和系统管理员可视化网络结构_支持多种网络设备图标和连接线_提供拖放式编辑和实.zip
MS-SQL Server

34,871

社区成员

254,637

社区内容

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

加载中

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

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

+ 用AI写文章