USP_SIMPLEDATALIST_FAFREGISTRATIONOPTIONDESCRIPTION

CREATE procedure dbo.USP_SIMPLEDATALIST_FAFREGISTRATIONOPTIONDESCRIPTION(
    @EVENTID uniqueidentifier,
    /*
      0: Company Leader
      1: Team Leader    
      2: Team Member
      3: Household Header
      4: Household Member       
      5: Individual


    */
    @REGISTRANTROLE smallint,
    @CURRENTREGISTRATIONTYPEID uniqueidentifier = null
)
as

  declare @sql nvarchar(max)
  declare @where nvarchar(200)

  if @CURRENTREGISTRATIONTYPEID is null
    set @CURRENTREGISTRATIONTYPEID = '00000000-0000-0000-0000-000000000000'

  set @sql = 'select EP.ID as VALUE,( EP.NAME + case when ERT.DESCRIPTION is null or ERT.DESCRIPTION = '''' then '''' else '' - '' end +  ERT.DESCRIPTION) as LABEL    
                from dbo.EVENTPRICE EP 
                 inner join dbo.FAFREGISTRATIONTYPE ERT on EP.ID = ERT.EVENTPRICEID
                 where EP.EVENTID = ''' + convert(varchar(36), @EVENTID) + '''
                 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 @where = '((' + @where + ' and dbo.UFN_REGISTRATIONTYPE_GETNUMBERLEFTBYID(EP.ID) > 0) or EP.ID=''' + convert(varchar(36), @CURRENTREGISTRATIONTYPEID) + ''')'

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

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