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