USP_SIMPLEDATALIST_REGISTRATIONINFORMATION

CREATE procedure dbo.USP_SIMPLEDATALIST_REGISTRATIONINFORMATION (
    @REGISTRATIONINFORMATIONTYPECODE tinyint = 0,
    @PROGRAMID uniqueidentifier = null,
    @EVENTID uniqueidentifier = null,
    @INCLUDEREGISTRATIONINFORMATIONID uniqueidentifier = null
) as

set nocount on;

select 
    ID as VALUE, 
    NAME as LABEL
from dbo.REGISTRATIONINFORMATION
where (ID = @INCLUDEREGISTRATIONINFORMATIONID)
    or (
        (TYPECODE = @REGISTRATIONINFORMATIONTYPECODE)
        and (INACTIVE = 0)
        and (ID not in (
            select ri.ID 
            from dbo.PROGRAMEVENTREGISTRATIONSECTION as pers
            inner join dbo.PROGRAMEVENTREGISTRATIONSECTIONREGISTRATIONINFORMATION as persri
                on pers.ID = persri.PROGRAMEVENTREGISTRATIONSECTIONID 
            inner join dbo.REGISTRATIONINFORMATION as ri
                on persri.REGISTRATIONINFORMATIONID = ri.ID
            where ((pers.PROGRAMID = @PROGRAMID) and (pers.PROGRAMID is not null))
                or ((pers.EVENTID = @EVENTID) and (pers.EVENTID is not null))
            ))
    )

order by NAME