use SubMeteringSystem
goif exists(select * from sys.views where name='V_BaseAmmeterAttribute')
drop view V_BaseAmmeterAttribute
gocreate 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'goif exists(select * from sys.views where name='V_AmmeterAttribute')
drop view V_AmmeterAttribute
gocreate view V_AmmeterAttribute
as
select ROW_NUMBER() over(order by F_Date) id,* from V_BaseAmmeterAttribute
goif exists (select * from sys.procedures where name='Pro_AmmeterAttribute')
drop proc Pro_AmmeterAttribute
gocreate 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
'
endend
else
begin
select @startTime=F_Date from V_AmmeterAttribute
where id=@idset @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
goif exists(select * from sys.views where name='V_BaseAmmeterAttribute')
drop view V_BaseAmmeterAttribute
gocreate 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'goif exists(select * from sys.views where name='V_AmmeterAttribute')
drop view V_AmmeterAttribute
gocreate view V_AmmeterAttribute
as
select ROW_NUMBER() over(order by F_Date) id,* from V_BaseAmmeterAttribute
goif exists (select * from sys.procedures where name='Pro_AmmeterAttribute')
drop proc Pro_AmmeterAttribute
gocreate 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
'
endend
else
begin
select @startTime=F_Date from V_AmmeterAttribute
where id=@idset @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
set @nextID=@id+1
set @id=@id+1这两行的 “1附近有语法错误”
我把这个1改成12或者其他数据 就不报错...
编译不报错 在数据库里执行的时候 错误是
set @nextID=@id+1
set @id=@id+1这两行的 “1附近有语法错误”
我把这个1改成12或者其他数据 就不报错...就是这样的异常 不出结果 找了很久没发现错误 所以我想看看有没有人能帮忙看下有什么错误