-- ==============================================================================================
-- 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)
-- ==============================================================================================
CREATE FUNCTION checkIfUpgradeRequired
(
    @objectType varchar(30),
    @objectName varchar(100),
    @patch_timestamp nvarchar(30),
    @patch_version nvarchar(10)
)
RETURNS int
AS
BEGIN
    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 FileName=@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))
            RETURN 1 -- sit version is older    

         RETURN 0 -- sit version is equal or newer  
    END

    RETURN 1 -- not in SIT
END

GO