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