MSSQL存储过程及游标、作业

1、存储过程

 --====  月存储过程  =====
alter  proc  [P_StatWaterCountMonthData]
@PointCode varchar(50), --点位编号
@ItemCode varchar(50), --项目编号
@TimeCondition varchar(1000),--读取时间的条件语句
@SaveTime varchar(50),--检测的时间节点
@EndTime varchar(50),--存储的时间节点
@SelTable varchar(50), 
@InsTable varchar(50) 
as
begin
	declare @rowCount int
	declare @CountSql nvarchar(500)
	declare @Sql nvarchar(2000)
	declare @StrengthSql nvarchar(2000)
 

	--删除原有数据
	set @CountSql='DELETE FROM '+@InsTable+' where 1=1 and '+@TimeCondition+'  and WaterSamplingID='''+@PointCode+'''  and WaterItemCode='''+@ItemCode+'''';
	print(@CountSql)
	exec(@CountSql) 
	
	 
	--插入新数据
	set @StrengthSql='  select 
		WaterSamplingID,WaterItemCode,1,null,null,
		null,null,Max(MaxValue),Min(MinValue),AVG(AvgValue),
		count(WaterSamplingID) count,null,null,null,null,
		'''+@SaveTime+''',0,count(WaterSamplingID) count,null,null,
		1,WaterSamplingID,
		------------统计 LevelCode---------------
		(select  
			case 
				when AVG(AvgValue)<= One_WaterLevel   then 1
				when AVG(AvgValue)<= Two_WaterLevel   then 2
				when AVG(AvgValue)<= Three_WaterLevel then 3
				when AVG(AvgValue)<= Four_WaterLevel then 4
				when AVG(AvgValue)<= Five_WaterLevel then 5
				else ''''
			end
		from T_Cod_riverOriginStandard where itemCode = WaterItemCode and StandardYear = (''GB3838-2002'')) 
		-----------------------------------------
		,AVG(AvgValue),AVG(AvgValue),'''+@EndTime+'''
		from T_Mid_RiversDayData where  WaterSamplingID='''+@PointCode+''' and WaterItemCode =  '''+@ItemCode+''' and   '+@TimeCondition+' 
		group by WaterSamplingID,WaterItemCode'
		
		set @Sql='insert into '+@InsTable+@StrengthSql;
		print(@Sql)
		exec(@Sql) 
end

 

 

2、游标（判断循环调用存储过程）

alter proc  [P_StatGasCountMonthData]
@PointCode varchar(50), --点位编号
@ItemCode varchar(50), --点位编号
@TimeCondition varchar(1000),--读取时间的条件语句
@CheckTime varchar(50),--检测的时间节点
@SelTable varchar(50), 
@InsTable varchar(50) 
as
begin
	declare @rowCount int
	declare @CountSql nvarchar(500)
	declare @Sql nvarchar(2000)
	declare @StrengthSql nvarchar(2000)
 
	--删除原有数据
	set @CountSql='DELETE FROM '+@InsTable+' where 1=1 and '+@TimeCondition+'  and GasPointCode='''+@PointCode+'''  and GasItemCode='''+@ItemCode+'''';
			print(@CountSql)
			exec(@CountSql) 
	
	  
	--插入新数据
	set @StrengthSql=' select GasPointCode,GasItemCode,1,'''+@CheckTime+''',Max(MaxValue),
			avg(AvgValue),MIN(MinValue),AVG(UpdateValue),GETDATE(),null,null,null,null,0,null,null'+ 
			' from '+@SelTable+'  where GasPointCode='''+@PointCode+''' and GasItemCode='''+@ItemCode+''' and '+@TimeCondition+' group by '+
			' GasPointCode,GasItemCode '
			set @Sql='insert into '+@InsTable+@StrengthSql;
			print(@Sql)
			exec(@Sql) 
end

 

 3、作业定时任务。