USP_DATALIST_MKTVENDOR

CREATE procedure dbo.USP_DATALIST_MKTVENDOR
as
  set nocount on;

  select 
    [VENDOR].[ID],
    [CONSTITUENT].[NAME],
    [VENDOR].[DESCRIPTION],
    (case when [CONSTITUENT].[ISINACTIVE] = 1 then 'Inactive' else 'Active' end) as [STATUS],
    convert(bit, case when exists (select top 1 1 from dbo.[MKTVENDORSERVICETYPE] where [VENDORID] = [VENDOR].[ID] and [SERVICETYPECODE] = 1) then 1 else 0 end) as [PROVIDESCREATIVES],
    convert(bit, case when exists (select top 1 1 from dbo.[MKTVENDORSERVICETYPE] where [VENDORID] = [VENDOR].[ID] and [SERVICETYPECODE] = 2) then 1 else 0 end) as [PROVIDESLISTS],
    convert(bit, case when exists (select top 1 1 from dbo.[MKTVENDORSERVICETYPE] where [VENDORID] = [VENDOR].[ID] and [SERVICETYPECODE] = 3) then 1 else 0 end) as [PROVIDESPUBLICMEDIA]
  from dbo.[VENDOR]
  inner join dbo.[CONSTITUENT] on [CONSTITUENT].[ID] = [VENDOR].[ID]
  order by [CONSTITUENT].[KEYNAME];

  return 0;