--EXEC MIG_MULTIPLE_REVISIONS_WITH_PREV_REVISION_EMPTY_FOLDER_Sp '', ''
CREATE OR ALTER PROCEDURE MIG_MULTIPLE_REVISIONS_WITH_PREV_REVISION_EMPTY_FOLDER_Sp
(
	@STATUS INT = 0 OUT,
	@MESSAGE NVARCHAR(999) = NULL OUT
)
AS 
BEGIN
	DECLARE @CNT_FOLDERS INT
	
	SET @STATUS = 0
	SET @MESSAGE = NULL
	
	SELECT @CNT_FOLDERS = COUNT(*)
	FROM FOLDER D
	JOIN
	(
	   SELECT FOLDER_KEY
	   FROM FOLDER
	   WHERE PREV_REVISION = ''
	   GROUP BY FOLDER_KEY HAVING COUNT(*) > 1
	) T
	ON T.FOLDER_KEY = D.FOLDER_KEY
	WHERE D.PREV_REVISION = ''

	IF @CNT_FOLDERS > 0
		BEGIN
			SET @STATUS = 1
			IF @MESSAGE IS NULL
				SET @MESSAGE = ''

			SET @MESSAGE = @MESSAGE + 'FOLDERS with more than one revision having PREV_REVISION field value as empty. '
		END

	SELECT @STATUS, @MESSAGE
END

