改错,有时间的来看下

just59277 2011-03-14 03:17:45
use SubMeteringSystem
go

if exists(select * from sys.views where name='V_BaseAmmeterAttribute')
drop view V_BaseAmmeterAttribute
go

create view V_BaseAmmeterAttribute
as
select top 1 * from T_ST_AmmeterAttribute
where F_BuildID='000001A001'
and F_Date<='2009/1/1'
UNION
select * from T_ST_AmmeterAttribute
where F_BuildID='000001A001'
and F_Date>'2009/1/1' and F_Date<'2010/1/1'

go

if exists(select * from sys.views where name='V_AmmeterAttribute')
drop view V_AmmeterAttribute
go

create view V_AmmeterAttribute
as
select ROW_NUMBER() over(order by F_Date) id,* from V_BaseAmmeterAttribute
go

if exists (select * from sys.procedures where name='Pro_AmmeterAttribute')
drop proc Pro_AmmeterAttribute
go

create procedure Pro_AmmeterAttribute
as
DECLARE @id int,
@sqlstring nvarchar(max),
@endTime nvarchar(30),
@startTime nvarchar(30),
@nextID int
set @id=1
set @id=1
set @sqlstring=''

while(@id<=(select max(id) from V_AmmeterAttribute))
begin
if(@id!=(select max(id) from V_AmmeterAttribute))
begin
select @startTime=F_Date from V_AmmeterAttribute
where id=@id

set @nextID=@id+1
select @endTime=F_Date from V_AmmeterAttribute
where id=@nextID



if(@id=1)
begin
set @sqlstring+='select t1.F_Value * t2.F_Rate from T_EC_EnergyItemResult t1,V_AmmeterAttribute t2
where t2.id= '+CONVERT(nvarchar,@id,4)+'
and t1.F_StartTime>= ''2009/1/1'' and t1.F_StartTime<'+@endTime+'
and t1.F_BuildID= ''000001A001''
UNION
'
end
else
begin
set @sqlstring+='select t1.F_Value * t2.F_Rate from T_EC_EnergyItemResult t1,V_AmmeterAttribute t2
where t2.id= '+CONVERT(nvarchar,@id,4)+'
and t1.F_StartTime>= '+@startTime+' and t1.F_StartTime<'+@endTime+'
and t1.F_BuildID= ''000001A001''
UNION
'

end

end
else
begin
select @startTime=F_Date from V_AmmeterAttribute
where id=@id

set @sqlstring+='select t1.F_Value * t2.F_Rate from T_EC_EnergyItemResult t1,V_AmmeterAttribute t2
where t2.id='+CONVERT(nvarchar,@id,4)+'
and t1.F_StartTime>='+@startTime+' and t1.F_StartTime<''2010/1/1''
and t1.F_BuildID= ''000001A001'' '
end
set @id=@id+1
end

exec(@sqlstring)

go
...全文
54 点赞 收藏 7
写回复
7 条回复
切换为时间正序
当前发帖距今超过3年,不再开放新的回复
发表回复
王向飞 2011-03-15
先说哪错?报啥错?
回复
lantian_019 2011-03-15
路过,顶起来哈!
回复
just59277 2011-03-15
[Quote=引用 6 楼 fredrickhu 的回复:]
有什么错误??会提示的
[/Quote]

编译不报错 在数据库里执行的时候 错误是
set @nextID=@id+1
set @id=@id+1

这两行的 “1附近有语法错误”
我把这个1改成12或者其他数据 就不报错...

就是这样的异常 不出结果 找了很久没发现错误 所以我想看看有没有人能帮忙看下有什么错误
回复
--小F-- 2011-03-15
有什么错误??会提示的
回复
快溜 2011-03-15
不应该。
回复
just59277 2011-03-15
编译不报错 在数据库里执行的时候 错误是
set @nextID=@id+1
set @id=@id+1

这两行的 “1附近有语法错误”
我把这个1改成12或者其他数据 就不报错...
回复
相关推荐
发帖
疑难问题
创建于2007-09-28

2.1w+

社区成员

MS-SQL Server 疑难问题
申请成为版主
帖子事件
创建了帖子
2011-03-14 03:17
社区公告
暂无公告