CREATE PROC [dbo].[up_DW2_DPM_DeployDtl_R01]
 @DeployID INT,
 @LandScapeName VARCHAR(50)
 
as
 
BEGIN
 
 DECLARE @RESULT BIT = 1
 
 DECLARE vCursorPtn CURSOR FAST_FORWARD
 FOR
SELECT ObjectName, [Version]
          FROM DeployDtl
          WHERE DeployID = @DeployID AND LandScapeName = @LandScapeName
          ORDER BY [Version] ASC

 
 OPEN vCursorPtn
 
 
DECLARE @vObjectName VARCHAR(50)
 DECLARE @vVersion INT
 
 
FETCH NEXT FROM vCursorPtn INTO @vObjectName, @vVersion
 
 
WHILE @@FETCH_STATUS = 0
 
BEGIN
     
  IF EXISTS (SELECT ObjectName
                  FROM DeployDtl
                  WHERE LandScapeName = @LandScapeName
                             AND ObjectName = @vObjectName AND @vVersion > [Version])

BEGIN
  
   IF EXISTS (SELECT a.DeployID
                   FROM DeployLog AS a
                            JOIN (SELECT ObjectName, MAX([Version]) AS [Version], DeployID, LandScapeName
                                     FROM DeployDtl
                                     WHERE LandScapeName = @LandScapeName AND ObjectName = @vObjectName
                                     GROUP BY ObjectName, DeployID, LandScapeName
                                     HAVING @vVersion > MAX([Version])) AS b
                                     ON a.DeployID = b.DeployID AND a.LandScapeName = b.LandScapeName)
   
   BEGIN
       SET @RESULT =1
   END
    
   ELSE
   BEGIN
       SET @RESULT = 0
   END
   
  END
  
  ELSE 
  BEGIN
      SET @RESULT = 0
  END
  
  FETCH NEXT FROM vCursorPtn INTO @vObjectName, @vVersion 
 END
 
 CLOSE vCursorPtn
 DEALLOCATE vCursorPtn
 
 SELECT @DeployID AS DeployID, @RESULT AS Result
 
END

AND