/**
*  This function is called to ensure that all line breaks are converted to CR/LF for possible multi-line
*  memo text columns.  MS O&I Sales, for example, sends us just a LF and MoversSuite memo controls will
*  just ignore it when there are multiple consecutive LFs.  So, we always remove the CR and then convert
*  the remaining LFs to CR/LF so that we always have only CR/LF.  If this causes the resulting string to
*  exceed @inMaxLength characters, then the string will be truncated and will only return @inMaxLength
*  characters.  Set @inMaxLength to 0 for varchar(max) size fields to forego length truncation.
*
*  @inSourceMemo: a varchar source string of any length or type (varchar(8000) or varchar(max).
*  @inMaxLength: the length to truncate the return value. Use 0 for varchar(max) otherwise it must be
*  between 1 and 8000.  Null or negative values are treated as if zero was passed in.
*/

CREATE FUNCTION [dbo].[HandleCRLFInMemos]( @inSourceMemo varchar(max), @inMaxLength int )
returns varchar(max) as
begin
    declare @theTempMemo varchar(max)
    if( @inSourceMemo is not null )
    begin
        set @inMaxLength = case
            when isnull( @inMaxLength, 0 ) <= 0 then 0
            when @inMaxLength > 8000 then 8000
            else @inMaxLength
        end
        set @theTempMemo = replace( replace( @inSourceMemo, 0x0D, '' ), 0x0A, 0x0D0A )
        if( @inMaxLength > 0 and len( @theTempMemo ) > @inMaxLength )
        begin
            set @theTempMemo = substring( @theTempMemo, 1, @inMaxLength )
        end
    end
    return @theTempMemo
end
GO
GRANT EXECUTE ON  [dbo].[HandleCRLFInMemos] TO [MssExec]
GO