辽源水资源数据同步

 

 

------------------------水位同步任务------------------------

declare @hp varchar(50)

declare @rand varchar(10)

declare @zd varchar(50)

declare @tablename varchar(50)

declare @sql varchar(250)

declare @zdtype varchar(10)

delete from  FH_SWZ_D where datediff(minute,TIME,getdate())<=60

 

DECLARE contact_cursor CURSOR FOR

select 序号,名称 from FH_SWZ

OPEN contact_cursor

FETCH NEXT FROM contact_cursor

INTO @rand,@zd

WHILE @@FETCH_STATUS = 0

BEGIN

if (len(@rand)=1)

  set @rand='00000'+@rand

if (len(@rand)=2)

  set @rand='0000'+@rand

if (len(@rand)=3)

  set @rand='000'+@rand

if (len(@rand)=4)

  set @rand='00'+@rand

if (len(@rand)=5)

  set @rand='0'+@rand

 

select @tablename='t_记录_定期存储_'+@rand+'_'+ ltrim(str(year(getdate())))

select @sql='insert into FH_SWZ_D(NAME,L,TIME) SELECT '''+@zd+''',瞬时降雨量,采集时间 from '+@tablename+' where datediff(minute,采集时间,getdate())<=60'

exec(@sql)

 

FETCH NEXT FROM contact_cursor

INTO @rand,@zd

END

CLOSE contact_cursor

DEALLOCATE contact_cursor

 

 

------------------------雨量同步任务------------------------

declare @nowtime varchar(50)

declare @totalsl  float

declare @oldsl float

declare @rand varchar(10)

declare @zd varchar(50)

declare @tablename varchar(50)

declare @sql Nvarchar(250)

declare @zdtype varchar(10)

declare @dfagcd varchar(8)

 

 

DELETE from FH_YLZ_D WHERE datediff(minute,[TIME],getdate())<59

 

DECLARE contact_cursor CURSOR FOR

select  序号,名称 from FH_YLZ

OPEN contact_cursor

FETCH NEXT FROM contact_cursor

INTO @rand,@zd

WHILE @@FETCH_STATUS = 0

BEGIN

if (len(@rand)=1)

  set @dfagcd='000'+@rand+'0001'

if (len(@rand)=2)

  set @dfagcd='00'+@rand+'0001'

if (len(@rand)=3)

  set @dfagcd='0'+@rand+'0001'

 

if (len(@rand)=1)

  set @rand='00000'+@rand

if (len(@rand)=2)

  set @rand='0000'+@rand

if (len(@rand)=3)

  set @rand='000'+@rand

 

select @oldsl=max(FWQT) FROM WS_SUMYL WHERE DFAGCD=@dfagcd

select @tablename='t_记录_定期存储_'+@rand+'_'+ ltrim(str(year(getdate())))

SET @SQL='select @totalsl=max(累积降雨量) from '+ QUOTENAME(@tablename)

 + ' where datediff(minute,记录时间,getdate())<59'

 

exec SP_EXECUTESQL @SQL,  N'@totalsl  float out',  @totalsl OUT

 

select @sql='insert into WS_SUMYL(DFAGCD,FWQT,DT) VALUES('''+@dfagcd+''','+cast(@totalsl as varchar(15))+',getdate())'

exec SP_EXECUTESQL @SQL

if @totalsl-@oldsl>=0 

begin

select @sql='insert into FH_YLZ_D(NAME,L,[TIME]) values('''+@zd+''','+cast(@totalsl-@oldsl as varchar(15))+',getdate())'

exec SP_EXECUTESQL  @sql 

 

end

else 

begin

select @sql='insert into FH_YLZ_D(NAME,L,[TIME]) values('''+@zd+''',0,getdate())'

exec SP_EXECUTESQL  @sql 

end

 

FETCH NEXT FROM contact_cursor

INTO @rand,@zd

END

CLOSE contact_cursor

DEALLOCATE contact_cursor

 

------------------------流量同步任务------------------------

 

declare @nowtime varchar(50)

declare @totalsl  float

declare @oldsl float

declare @rand varchar(10)

declare @zd varchar(50)

declare @tablename varchar(50)

declare @sql Nvarchar(250)

declare @zdtype varchar(10)

declare @dfagcd varchar(8)

 

 

DELETE from WR_RLTW_C WHERE datediff(minute,DT,getdate())<59

DECLARE contact_cursor CURSOR FOR

select  DFAGCD,DFAGNM from WR_DFAG_B

OPEN contact_cursor

FETCH NEXT FROM contact_cursor

INTO @rand,@zd

WHILE @@FETCH_STATUS = 0

BEGIN

select @dfagcd=@rand+'0001'

if (len(@rand)=1)

  set @rand='00000'+@rand

if (len(@rand)=2)

  set @rand='0000'+@rand

if (len(@rand)=3)

  set @rand='000'+@rand

if (len(@rand)=4)

  set @rand='00'+@rand

if (len(@rand)=5)

  set @rand='0'+@rand

select @oldsl=max(FWQT) FROM WS_SUMSL WHERE DFAGCD=@dfagcd

select @tablename='t_记录_定期存储_'+@rand+'_'+ ltrim(str(year(getdate())))

SET @SQL='select @totalsl=max(累积流量) from '+ QUOTENAME(@tablename)

 + ' where datediff(minute,记录时间,getdate())<59'

 

exec SP_EXECUTESQL @SQL,  N'@totalsl  float out',  @totalsl OUT

select @sql='insert into WS_SUMSL(FWTYP,DFAGCD,FWQT,DT) VALUES(''1'','''+@dfagcd+''','+ltrim(STR(@totalsl))+',getdate())'

exec SP_EXECUTESQL @SQL

 

if @totalsl-@oldsl>=0 

begin

select @sql='insert into WR_RLTW_C(FWTYP,DFAGCD,FWQT,DT,YEAR,MONTH,DAY,QUARTER) values(''1'','''

+@dfagcd+''','+ltrim(STR(@totalsl-@oldsl))+',getdate(),year(getdate()),month(getdate()),day(getdate()),month(getdate())/3+1)'

exec SP_EXECUTESQL  @sql 

end

 

FETCH NEXT FROM contact_cursor

INTO @rand,@zd

END

CLOSE contact_cursor

DEALLOCATE contact_cursor

 

 

 

备注，sp_executesql可以传入和传出动态参数

exec  SP_EXECUTESQL    N'select  @count=count(1)  from  tb  where  id>55',  N'@count  int  out',  @count out