-- ==============================================================================================
-- Author:    Monica Lupu
-- Create date: 06/06/2008
-- Description: checks if an object needs to be installed/upgraded
-- Returns: 0, upgrade not required (same or newer version in SIT)
--        1, install/upgrade required (object not found or there is an older version in SIT)
--
-- 29-jan-09 fix (lupmo01): Star 17868021-SIT filename case differs, fixes 17194010
-- Added a new procedure (checkIfUpgradeRequired2) for logging messages to announce when upgrade is NOT required
-- Keep checkIfUpgradeRequired function for patches released before 17868021
-- ==============================================================================================
CREATE PROCEDURE [dbo].[checkIfUpgradeRequired2]
(
    @objectType varchar(30),
    @objectName varchar(100),
    @patch_timestamp nvarchar(30),
    @patch_version nvarchar(10),
    @errorCodeVal int OUTPUT
)
AS
BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    SET NOCOUNT ON;
    PRINT 'checkIfUpgradeRequired '  +
                  @objectType + ', ' +
          @objectName + ', ' +
                  @patch_timestamp + ', ' +
          @patch_version   
          
    declare @sit_timestamp nvarchar(30), @sit_version nvarchar(10)            

    if (CHARINDEX('.xml', @objectName) = 0)
          set @objectName = @objectName + '.xml'

    select @sit_timestamp = FileTimestamp, @sit_version = FileVersion
    from mdb_schema_information
    where lower(FileName)=lower(@objectName) and FileType=@objectType
      
    if (@sit_timestamp is NOT NULL)
    BEGIN        
        if (@sit_version < @patch_version or
               (@sit_version = @patch_version and dbo.compare_timestamp(@sit_timestamp, @patch_timestamp) = 0))
            begin
                PRINT 'Upgrade ' + @objectName + ' is required. SIT version is older.';   
                set @errorCodeVal = 1 -- sit version is older  
                RETURN
            end
        else
            begin
                PRINT 'Upgrade ' + @objectName + ' is NOT required. SIT version is equal or newer.';
                set @errorCodeVal = 0 -- sit version is equal or newer  
                RETURN
            end
    END

    set @errorCodeVal = 1
    PRINT @objectName + ' is NOT in SIT'     
END

GO