SQLServer之GOTO用法
GOTO会影响执行效率,不建议用。
CREATE PROC SP_BGY_PM_StateChangeApplication_CycleAnalysis @FQID VARCHAR(256)---WN210225000071 AS BEGIN SELECT NEWID()AS PK,ProjectStatu,[FI_EndTime] INTO #Temp_TTAA FROM BGY_PM_StateChangeApplication WHERE FQ_ID=@FQID ORDER BY [FI_EndTime] ASC --SELECT NEWID()AS PK,ID,TITLE AS ProjectStatu,[Date] AS FI_EndTime INTO #Temp_TTAA FROM AATEST WHERE ID=2 ORDER BY [Date] ASC SELECT * FROM #Temp_TTAA DECLARE @CountNum INT DECLARE @SountNum_Sub INT DECLARE @Id VARCHAR(256) DECLARE @NodeName VARCHAR(256) DECLARE @TempTime DATETIME DECLARE @StartTime DATETIME DECLARE @EndTime DATETIME DECLARE @DaySum INT=0 INTIT: SELECT @CountNum=COUNT(0) FROM #Temp_TTAA --SELECT @CountNum WHILE(@CountNum>0) BEGIN SELECT TOP(1) @Id=PK,@NodeName=ProjectStatu,@TempTime=[FI_EndTime] FROM #Temp_TTAA IF(@NodeName='3' OR @NodeName='5')--2:进行中,3:暂停,5:已作废 BEGIN SET @StartTime=@TempTime SELECT @StartTime AS '暂停开始时间' --SELECT * FROM #Temp_TTAA DELETE FROM #Temp_TTAA WHERE PK=@Id----移除本次记录 --SELECT * FROM #Temp_TTAA SELECT @SountNum_Sub=COUNT(0) FROM #Temp_TTAA WHILE(@SountNum_Sub>0) BEGIN SELECT TOP(1) @Id=PK, @NodeName=ProjectStatu,@TempTime=[FI_EndTime] FROM #Temp_TTAA IF(@NodeName='2') BEGIN SET @EndTime=@TempTime SELECT @EndTime AS '进行中开始时间' SET @DaySum=@DaySum+DATEDIFF(DAY,@StartTime,@EndTime) DELETE FROM #Temp_TTAA WHERE PK=@Id SET @SountNum_Sub=0 GOTO INTIT END DELETE FROM #Temp_TTAA WHERE PK=@Id SELECT @SountNum_Sub=COUNT(0) FROM #Temp_TTAA END END DELETE FROM #Temp_TTAA WHERE PK=@Id ---非暂停,则删除记录 SELECT @CountNum=COUNT(0) FROM #Temp_TTAA--继续下一循环 END SELECT @DaySum ---BGY_PM_StateChangeApplication_CycleAnalysis IF EXISTS(SELECT 1 FROM BGY_PM_StateChangeApplication_CycleAnalysis WHERE FQID=@FQID) UPDATE BGY_PM_StateChangeApplication_CycleAnalysis SET Cycle=@DaySum,UpdateTime=GETDATE() WHERE FQID=@FQID ELSE INSERT INTO BGY_PM_StateChangeApplication_CycleAnalysis(FQID,Cycle,UpdateTime) VALUES(@FQID,@DaySum,GETDATE()) DROP TABLE #Temp_TTAA---释放临时表 END