UFN_SPONSORSHIPOPPORTUNITY_GETNAME_FORWEB

      create function [dbo].[UFN_SPONSORSHIPOPPORTUNITY_GETNAME_FORWEB](@ID uniqueidentifier) returns nvarchar(50) 
        as
        begin

        declare @s nvarchar(100);
        declare @st int;

        select @st = SPONSORSHIPOPPORTUNITYGROUP.SPONSORSHIPOPPORTUNITYTYPECODE
        from dbo.SPONSORSHIPOPPORTUNITY
        inner join dbo.SPONSORSHIPOPPORTUNITYGROUP on SPONSORSHIPOPPORTUNITYGROUP.ID = SPONSORSHIPOPPORTUNITY.SPONSORSHIPOPPORTUNITYGROUPID
        where SPONSORSHIPOPPORTUNITY.ID = @ID;

          if @st=1
              begin
                select @s = case ch.FIRSTNAME when '' then ch.LASTNAME else ch.FIRSTNAME end
                from SPONSORSHIPOPPORTUNITYCHILD ch
                where ch.ID=@ID
              end
          else
              begin
                select @s = NAME from SPONSORSHIPOPPORTUNITYPROJECT where ID=@ID
              end
            return @s;
        end