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
'IT > MS-SQL' 카테고리의 다른 글
SP 실행 권한 문제 (0) | 2011.03.08 |
---|---|
MSSQL 단축키 (0) | 2011.03.02 |
내부 프로시저 실행시킬 때 EXECUTE 주의점 (0) | 2011.02.28 |
MSSQL SELECT절에서 두번째로 높은 값 찾기 (0) | 2011.02.24 |
MSSQL 특정 단어가 포함된 프로시저 찾기 (0) | 2011.02.22 |