USP_SIMPLEDATALIST_MKTVENDORACTIVE

CREATE procedure dbo.[USP_SIMPLEDATALIST_MKTVENDORACTIVE]
(
  @LISTID uniqueidentifier = null,
  @CREATIVEID uniqueidentifier = null,
  @SERVICETYPECODE tinyint = null
)
as
  set nocount on;

  select 
    [VENDOR].[ID] as [VALUE], 
    [CONSTITUENT].[NAME] as [LABEL] 
  from 
    dbo.[VENDOR]
  inner join
    dbo.[CONSTITUENT] on [VENDOR].[ID] = [CONSTITUENT].[ID]
  where
    ([CONSTITUENT].[ISINACTIVE] = 0 or
    ([VENDOR].[ID] = (select [VENDORID] from dbo.[MKTLIST] where [ID] = @LISTID)) or
    ([VENDOR].[ID] = (select [VENDORID] from dbo.[MKTCREATIVE] where [ID] = @CREATIVEID)))
    and
    (@SERVICETYPECODE is null or exists (select top 1 1 from dbo.[MKTVENDORSERVICETYPE] where [MKTVENDORSERVICETYPE].[VENDORID] = [VENDOR].[ID] and [SERVICETYPECODE] = @SERVICETYPECODE))
  order by [CONSTITUENT].[KEYNAME];

  return 0;