USP_DATALIST_FAFREGISTRATIONOPTIONDESCRIPTION

CREATE procedure dbo.USP_DATALIST_FAFREGISTRATIONOPTIONDESCRIPTION
(
    @EVENTID uniqueidentifier,
    /*
      0: Company Leader
      1: Team Leader    
      2: Team Member
      3: Household Header
      4: Household Member       
      5: Individual
    */
    @REGISTRANTROLE smallint 
) 
as  
set nocount on; 
  declare @sql nvarchar(max)
  declare @where nvarchar(200)
  set @sql = 'select EP.ID as VALUE,( EP.NAME + '' - '' +  ERT.DESCRIPTION) as LABEL,
                ERT.ISNOTAVAILABLEONLINEREG as OFFLINEREG
                from dbo.EVENTPRICE EP 
                 inner join dbo.FAFREGISTRATIONTYPE ERT on EP.ID = ERT.EVENTPRICEID
                 where EP.EVENTID = ''' + convert(varchar(36), @EVENTID) + '''
                 and ERT.ISACTIVE =1 and '    
    if @REGISTRANTROLE = 0
       set @where = 'ERT.ISREGTYPEAVAILABLECOMPANYLEADER = 1'
    else if @REGISTRANTROLE  =1
       set @where = 'ERT.ISREGTYPEAVAILABLETEAMLEADER = 1'
    else if @REGISTRANTROLE = 2
       set @where = 'ERT.ISREGTYPEAVAILABLETEAMMEMBER = 1'
    else if @REGISTRANTROLE = 3
       set @where = 'ERT.ISREGTYPEAVAILABLEHEADHOUSEHOLD = 1'
    else if @REGISTRANTROLE = 4
      set @where = 'ERT.ISREGTYPEAVAILABLEFAMILYMEMBER = 1'
    else if @REGISTRANTROLE  =5
       set @where = 'ERT.ISREGTYPEAVAILABLEINDIVIDUAL = 1'

  else
    set @where = '1 = 1'

  set @sql = @sql + @where + ' order by LABEL'

  declare @temp table (ID uniqueidentifier, NAME nvarchar(200), OFFLINEREG bit)
  insert into @temp
     values('00000000-0000-0000-0000-000000000000','', 0)
   insert into @temp execute (@sql)
  select ID as VALUE, NAME as LABEL, OFFLINEREG from @temp