/**
*
*  This procedure is called to add the document image/content to the document system when the MSSDocument already
*  exists and wrapping it all in a transaction.  Thus we need to update the document with the content identifier after
*  the image/content has been added.
*
*  @param @inMssDocumentID    The primary key of this document.  Required.
*  @param @inDocumentImage    The binary content of this document.  Required.
*
*  Returned results:
*  ImageID        The MSSDocumentImageID assigned to this image.
*    ErrorCode    If there was an error (>0) or not (0).
*        Possible error codes:
*            0:    Success; no error.
*            1:    Unable to add image (see DocumentManagementInsertImage SP).
*            2:    Unable to find MSSDocument from @inMssDocumentID.
*            3:    @inMssDocumentID was not supplied.  It is required.
*            4:    @inDocumentImage was not supplied.  It is required.
*/

CREATE PROCEDURE [dbo].[DocumentManagementAddImageUpdateDoc]
    @inMssDocumentID int,
    @inDocumentImage image
as
set nocount on

declare @theEnsureUniqueDocumentName bit = 1
declare @theOrderID int
declare @theDocumentName varchar(128)
declare @theImageID int
declare @theTempImageID int
declare @theErrorCode int

-- Initialize output results.
select
    @theErrorCode = 2

if( @inMssDocumentID is null )
begin
    set @theErrorCode = 3
end
else if( @inDocumentImage is null )
begin
    set @theErrorCode = 4
end
else
begin
    begin transaction

        -- This select will lock this document.
        select
            @theOrderID = MSSDocument.OrderFID,
            @theDocumentName = MSSDocument.DocumentName,
            @theImageID = MSSDocument.MSSDocumentImageFID
        from MSSDocument
        where MSSDocument.MSSDocumentID = @inMssDocumentID

        if( @theImageID is not null )
        begin
            -- Someone else beat us to this point.  Just return their @theImageID value.
            set @theErrorCode = 0
        end
        else if( @theDocumentName is not null )
        begin
            -- MSSDocument exists so we can continue.
            exec DocumentManagementInsertImage
                @inOrderID = @theOrderID,
                @inDocumentName = @theDocumentName,
                @inDocumentImage = @inDocumentImage,
                @outImageID = @theImageID output,
                @outError = @theErrorCode output,
                @inEnsureUniqueDocumentName = @theEnsureUniqueDocumentName

            if( @theErrorCode = 0 )
            begin
                -- Update the DocByteSize since we have the image right here.
                -- Who knows if it was right, was missing or changed since
                -- the MSSDocument record was originally added.
                update MSSDocument set
                    MSSDocumentImageFID = @theImageID,
                    DocByteSize = datalength( @inDocumentImage )
                where MSSDocument.MSSDocumentID = @inMssDocumentID
            end
        end

    commit transaction

    if( @theErrorCode = 1 )
    begin
        -- See if another user was successful.  If so, do we care if we were not?
        select
            @theTempImageID = MSSDocument.MSSDocumentImageFID
        from MSSDocument
        where MSSDocument.MSSDocumentID = @inMssDocumentID

        if( @theTempImageID is not null )
        begin
            -- MSSDocument had it's image identifier updated.  Return that.
            select
                @theErrorCode = 0,
                @theImageID = @theTempImageID
        end
    end
end

select
    ImageID = @theImageID,
    ErrorCode = @theErrorCode
GO
GRANT EXECUTE ON  [dbo].[DocumentManagementAddImageUpdateDoc] TO [MssExec]
GO