/**
*    This procedure returns the document content size in bytes of a document stored in
*    the document manangement system of MoversSuite.  This is only used when a new
*    document is added to our Document Management System.
*
*    @param @inMSSDocumentImageID The primary key to the MSSDocumentImage record for
*    this document.
*    returns:
*    @outDocByteSize: The integer size, in bytes, of the document's content.
*/
create procedure [dbo].[GetDocumentByteSize]
    @inMSSDocumentImageID int,
    @outDocByteSize int output
as
set nocount on;

set xact_abort on
begin try
    declare @theExternalDBImageID int
    declare @theExternalDBImageDB sysname

    select
        @theExternalDBImageID = MSSDocumentImage.ExtImageFID,
        @theExternalDBImageDB = isnull( MSSDocumentImageDB.DatabaseName, dbo.GetDocDbName() ),
        @outDocByteSize = datalength( MSSDocumentImage.DocumentImage )
    from MSSDocumentImage
    left outer join MSSDocumentImageDB on MSSDocumentImageDB.MSSDocumentImageDBID = MSSDocumentImage.MSSDocumentImageDBFID
    where
        MSSDocumentImage.MSSDocumentImageID = @inMSSDocumentImageID and
        ( MSSDocumentImageDB.MSSDocumentImageDBID is null or MSSDocumentImageDB.IsRemoved = 0 )

    if(
        @outDocByteSize is null and
        @theExternalDBImageID is not null and
        exists ( select top 1 1 from sys.databases where name = @theExternalDBImageDB ) )
    begin
        -- The document content is stored in one of the document image databases. Use dynamic sql to grab it.
        declare @theDynamicSql nvarchar(500)
        set @theDynamicSql = N'set @outDocByteSize = null
            select @outDocByteSize = datalength( DocumentImage.DocumentImage )
            from ' + @theExternalDBImageDB + N'..DocumentImage
            where DocumentImage.DocumentImageID = @inImageID'

        exec sp_executesql @theDynamicSql, N'@inImageID int, @outDocByteSize int output',
            @theExternalDBImageID,
            @outDocByteSize output
    end

end try
begin catch
    -- We tried.
    set @outDocByteSize = null
end catch
GO
GRANT EXECUTE ON  [dbo].[GetDocumentByteSize] TO [MssExec]
GO