UFN_MKTSOURCECODE_INUSEBYEFFORT

create function dbo.[UFN_MKTSOURCECODE_INUSEBYEFFORT]
(
  @SOURCECODEID uniqueidentifier
)
/*
  Returns true if source code is in use by an effort.
*/
returns bit
as begin
  declare @SOURCECODEINUSE bit;

  set @SOURCECODEINUSE = 
    case when exists (select top 1 [MKTMARKETINGPLANITEM].[ID] from dbo.[MKTMARKETINGPLANITEM] where [MKTMARKETINGPLANITEM].[SOURCECODEID] = @SOURCECODEID) then 1 
         when exists (select top 1 [MKTSEGMENTATION].[ID] from dbo.[MKTSEGMENTATION] where [MKTSEGMENTATION].[SOURCECODEID] = @SOURCECODEID) then 1 
         when exists (select top 1 [MKTACKNOWLEDGEMENTMAILINGTEMPLATE].[ID] from dbo.[MKTACKNOWLEDGEMENTMAILINGTEMPLATE] where [MKTACKNOWLEDGEMENTMAILINGTEMPLATE].[SOURCECODEID] = @SOURCECODEID) then 1 
         when exists (select top 1 [MKTMEMBERSHIPMAILINGTEMPLATE].[ID] from dbo.[MKTMEMBERSHIPMAILINGTEMPLATE] where [MKTMEMBERSHIPMAILINGTEMPLATE].[SOURCECODEID] = @SOURCECODEID) then 1 
         when exists (select top 1 [MKTSPONSORSHIPMAILINGTEMPLATE].[ID] from dbo.[MKTSPONSORSHIPMAILINGTEMPLATE] where [MKTSPONSORSHIPMAILINGTEMPLATE].[SOURCECODEID] = @SOURCECODEID) then 1 
         else 0 end;

  return @SOURCECODEINUSE;
end