我把2005的一个数据库通过生成脚本转移到了sql2000中,表存储过程等都创建成功了,但是有下面一个存储过程在sql2000中创建成功却在执行的时候会有‘列名 'sort' 无效。’的错误,而在sql2005中很正常。是因为临时表的问题么?应该怎样修改呢?O(∩_∩)O谢谢~
以下是存储过程代码:
--首页房屋出租出售页信息
CREATE proc [dbo].[HomeQZInfo]
@fidName varchar(50),
@PageSize int,
@PageIndex int,
@OrderType int,
@strWhere varchar(1500),
@rows int output,
@pagecount int output
as
--C_Qiuzu 1
select ID as pid,[Type],ExpectAddr as Address,Rentrange
as Price,Room,Hall,Toilet,[Floor],CreateTime,Floorarea,Status,[Property]
into #tmpqz from C_Qiuzu where Status!=2 and CreateTime<=Validity
--添加区分列。插入默认值为0,constraint
alter table #tmpqz ADD sort int NULL DEFAULT 0 WITH VALUES
update #tmpqz set sort=1 where sort=0
--取消标识列
SET IDENTITY_INSERT #tmpqz ON
--U_Qiuzu 2 表数据
insert into #tmpqz(pid,[Type],Address,Price,Room,Hall,Toilet,
[Floor],CreateTime,Floorarea,Status,[Property] )
select ID,[Type],ExpectAddr,Rentrange,Room,Hall,Toilet,[Floor],CreateTime,
Floorarea,Status,[Property] from U_Qiuzu
where Status!=2 and CreateTime<=Validity
--更改区分列
update #tmpqz set sort=2 where sort=0
---U_SecondBuy 3表数据
insert into #tmpqz(pid,[Type],Address,Price,Room,Hall,Toilet,[Floor],
CreateTime,Floorarea,Status,[Property])
select ID,[Type],Address,Pricerange,Room,Hall,Toilet,[Floor],CreateTime,
Floorarea,Status,[Property] from U_SecondBuy
where Status!=2 and CreateTime<=Validity
----更改区分列的值
update #tmpqz set sort=3 where sort=0
---C_SecondBuy 4表数据
insert into #tmpqz(pid,[Type],Address,Price,Room,Hall,Toilet,[Floor],
CreateTime,Floorarea,Status,[Property])
select ID,[Type],Address,Pricerange,Room,Hall,Toilet,[Floor],CreateTime,
Floorarea,Status,[Property] from C_SecondBuy
where Status!=2 and CreateTime<=Validity
--更改区分列
update #tmpqz set sort=4 where sort=0
exec pagination '#tmpqz','*',@fidName,@PageSize,@PageIndex,@OrderType,@strWhere,@rows output,@pagecount output
drop table #tmpqz
GO
以下是存储过程代码:
--首页房屋出租出售页信息
CREATE proc [dbo].[HomeQZInfo]
@fidName varchar(50),
@PageSize int,
@PageIndex int,
@OrderType int,
@strWhere varchar(1500),
@rows int output,
@pagecount int output
as
--C_Qiuzu 1
select ID as pid,[Type],ExpectAddr as Address,Rentrange
as Price,Room,Hall,Toilet,[Floor],CreateTime,Floorarea,Status,[Property]
into #tmpqz from C_Qiuzu where Status!=2 and CreateTime<=Validity
--添加区分列。插入默认值为0,constraint
alter table #tmpqz ADD sort int NULL DEFAULT 0 WITH VALUES
update #tmpqz set sort=1 where sort=0
--取消标识列
SET IDENTITY_INSERT #tmpqz ON
--U_Qiuzu 2 表数据
insert into #tmpqz(pid,[Type],Address,Price,Room,Hall,Toilet,
[Floor],CreateTime,Floorarea,Status,[Property] )
select ID,[Type],ExpectAddr,Rentrange,Room,Hall,Toilet,[Floor],CreateTime,
Floorarea,Status,[Property] from U_Qiuzu
where Status!=2 and CreateTime<=Validity
--更改区分列
update #tmpqz set sort=2 where sort=0
---U_SecondBuy 3表数据
insert into #tmpqz(pid,[Type],Address,Price,Room,Hall,Toilet,[Floor],
CreateTime,Floorarea,Status,[Property])
select ID,[Type],Address,Pricerange,Room,Hall,Toilet,[Floor],CreateTime,
Floorarea,Status,[Property] from U_SecondBuy
where Status!=2 and CreateTime<=Validity
----更改区分列的值
update #tmpqz set sort=3 where sort=0
---C_SecondBuy 4表数据
insert into #tmpqz(pid,[Type],Address,Price,Room,Hall,Toilet,[Floor],
CreateTime,Floorarea,Status,[Property])
select ID,[Type],Address,Pricerange,Room,Hall,Toilet,[Floor],CreateTime,
Floorarea,Status,[Property] from C_SecondBuy
where Status!=2 and CreateTime<=Validity
--更改区分列
update #tmpqz set sort=4 where sort=0
exec pagination '#tmpqz','*',@fidName,@PageSize,@PageIndex,@OrderType,@strWhere,@rows output,@pagecount output
drop table #tmpqz
GO
解决方案 »
- 留言板加载留言表情
- .net(C#)怎么实现只输入目录就能默认访问该目录下的某个文件
- 网页出现警告,怎么解决?急急急急急急急。。。。
- 网站身份验证问题(难)
- ==== ASP.NET 2.0 中复合组件状态问题? (不贴边者不给分) ====
- 一个奇怪的问题,SESSION丢失问题。
- 请问各位一个关于asp.net的问题!谢谢!
- 关于数据查询记录与缓存相结合的问题,强烈要求管理员置顶几天(缓存在.NET中对提高性能非常关键,但在网上很维找到贴近实际的例子。)
- 谁给一段asp.net操作Sql Server的代码我看看
- c# gridView 取值
- asp.net 用户控件缓存可以禁用吗?我缓存了整个页面了..发现
- 菜鸟求助!如何给页面上的imagebutton遍历赋值。。。
注意单引号的使用~
就是在玩你!!!不信大家来看:1> select ID as pid,[Type],ExpectAddr as Address,Rentrange
as Price,Room,Hall,Toilet,[Floor],CreateTime,Floorarea,Status,[Property]
into #tmpqz from C_Qiuzu where Status!=2 and CreateTime<=Validity2> alter table #tmpqz ADD sort int NULL DEFAULT 0 WITH VALUES 3> update #tmpqz set sort=1 where sort=0为什么要这样分三步??为什么象下面一口气搞定???select ID as pid,[Type],ExpectAddr as Address,Rentrange as Price,Room,Hall,Toilet,[Floor],CreateTime,Floorarea,Status,[Property],1 as sort
into #tmpqz from C_Qiuzu where Status!=2 and CreateTime<=Validity
那我想问你,
象我那样一步到位,
有什么问题?你后面不能再update吗??
into #tmpqz ......我写的里面有 1 as sort 这段,
你是不是没看见
但是为什么会出现‘列名sort无效’这个错误呢?
你第一次ADD一个SORT列进临时表,
后面再INSERT,却没给这个列赋值!!!!--U_Qiuzu 2 表数据
insert into #tmpqz(pid,[Type],Address,Price,Room,Hall,Toilet,
[Floor],CreateTime,Floorarea,Status,[Property] )
select ID,[Type],ExpectAddr,Rentrange,Room,Hall,Toilet,[Floor],CreateTime,
Floorarea,Status,[Property] from U_Qiuzu
where Status!=2 and CreateTime<=Validity这句里面,什么地方给SORT赋值拉????????
CREATE proc [dbo].[HomeQZInfo]
@fidName varchar(50),
@PageSize int,
@PageIndex int,
@OrderType int,
@strWhere varchar(1500),
@rows int output,
@pagecount int output
as
--C_Qiuzu 1
select ID as pid,[Type],ExpectAddr as Address,Rentrange
as Price,Room,Hall,Toilet,[Floor],CreateTime,Floorarea,Status,[Property],
1 as sort
into #tmpqz from C_Qiuzu where Status!=2 and CreateTime<=Validity
--取消标识列
SET IDENTITY_INSERT #tmpqz ON
--U_Qiuzu 2 表数据
insert into #tmpqz(pid,[Type],Address,Price,Room,Hall,Toilet,
[Floor],CreateTime,Floorarea,Status,[Property],sort )
select ID,[Type],ExpectAddr,Rentrange,Room,Hall,Toilet,[Floor],CreateTime,
Floorarea,Status,[Property],2 as sort from U_Qiuzu
where Status!=2 and CreateTime<=Validity---U_SecondBuy 3表数据
insert into #tmpqz(pid,[Type],Address,Price,Room,Hall,Toilet,[Floor],
CreateTime,Floorarea,Status,[Property])
select ID,[Type],Address,Pricerange,Room,Hall,Toilet,[Floor],CreateTime,
Floorarea,Status,[Property],3 as sort from U_SecondBuy
where Status!=2 and CreateTime<=Validity---C_SecondBuy 4表数据
insert into #tmpqz(pid,[Type],Address,Price,Room,Hall,Toilet,[Floor],
CreateTime,Floorarea,Status,[Property])
select ID,[Type],Address,Pricerange,Room,Hall,Toilet,[Floor],CreateTime,
Floorarea,Status,[Property],4 as sort from C_SecondBuy
where Status!=2 and CreateTime<=Validityexec pagination '#tmpqz','*',@fidName,@PageSize,@PageIndex,@OrderType,@strWhere,@rows output,@pagecount output
drop table #tmpqz
GO