CREATE PROCEDURE update_mdb_sit
    -- Add the parameters for the stored procedure here
    @objName nvarchar(100),
    @objType nvarchar(30),
    @fileTimestampStr nvarchar(30),
    @schemaName nvarchar(30),
    @xmlData ntext,
    @fileLocation nvarchar(30)='Local',
    @fileVersion nvarchar(10)='1.0'
AS
DECLARE
    @MDB_SITCursor cursor,
    @fileStatus nvarchar(30),
    @updatedFileStatus nvarchar(30),
    @errorCodeVal int
BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    SET NOCOUNT ON;

    if (CHARINDEX('.xml', @objName) = 0)
        SET @objName = @objName + '.xml'

    /**
         * Determine if row already exists
     */
    SET @MDB_SITCursor = CURSOR FAST_FORWARD
        FOR
    Select FileStatus
    from dbo.mdb_schema_information
    where lower(FileName)=lower(@objName) AND
    filetype=@objType

    OPEN @MDB_SITCursor
    FETCH NEXT FROM @MDB_SITCursor
    INTO @fileStatus

    IF @@ERROR <> 0
    BEGIN
        SET @errorCodeVal = @@ERROR
        -- Return 99 to the calling program to indicate failure.
        PRINT 'Unable to read from the mdb_schema_information table. Error code: ' + @errorCodeVal
        RETURN(@errorCodeVal)
    END

    IF @@FETCH_STATUS = 0
        SET @updatedFileStatus = 'Patched'
    ELSE
        SET @updatedFileStatus = 'Installed'
    CLOSE @MDB_SITCursor
    DEALLOCATE @MDB_SITCursor

    /**
     *  Insert data into mdb_schema_information
     */
    IF @updatedFileStatus = 'Patched'
       DELETE FROM dbo.mdb_schema_information
           where lower(FileName) = lower(@objName) AND
           FileType = @objType;

    INSERT INTO dbo.mdb_schema_information
        (FileName, FileStatus, FileType, FileLocation,
        FileVersion, FileTimestamp, InstalledTimestamp, SchemaName,
        XMLData)

    VALUES (@objName,
        @updatedFileStatus,
        @objType,
        @fileLocation,
        @fileVersion,
        @fileTimestampStr,
        getdate(),
        @schemaName,
        @xmlData)
            
    IF @@ERROR <> 0
    BEGIN
        SET @errorCodeVal = @@ERROR
        -- Return 99 to the calling program to indicate failure.
        PRINT 'Unable to update the mdb_schema_information table. Error code: ' + @errorCodeVal
        RETURN(@errorCodeVal)
    END

    /**
      * Exit with success
     **/
    RETURN(0)
END

GO