UFN_ACQUISITIONLISTMEMBERCONSTITUENT_BUILDNAME

            create function dbo.UFN_ACQUISITIONLISTMEMBERCONSTITUENT_BUILDNAME(@ID uniqueidentifier)
            returns nvarchar(154)
            as begin
                if @ID is null
                    return '';

                declare @name nvarchar(154);

                with CONSTITUENTS (NAME) AS
                    (select [NAME] from dbo.[CONSTITUENT] where [ID]=@ID
                        union 
                        select [NAME] from dbo.[MKTFINDERNUMBERCONSTITUENT] where [ID]=@ID
                    )
                select top 1 @name=NAME from CONSTITUENTS;

                return coalesce(@name, '');
            end