USP_GETCONSENT_BY_CONSTITUENTID

CREATE procedure dbo.USP_GETCONSENT_BY_CONSTITUENTID
(
  @ConstituentID uniqueidentifier
)
as

  select CSC.[ID]
        ,CSC.[CONSTITUENTID]
        ,CSC.[SOLICITCODEID]
        ,CSC.[SEQUENCE]
        ,CSC.[ADDEDBYID]
        ,CSC.[CHANGEDBYID]
        ,CSC.[DATEADDED]
        ,CSC.[DATECHANGED]
        ,CSC.[TS]
        ,CSC.[TSLONG]
        ,CSC.[STARTDATE]
        ,CSC.[ENDDATE]
        ,CSC.[COMMENTS]
        ,case when SC.CONSENTCODE = 0 then 2
                else CSC.[CONSENTPREFERENCECODE] end as [CONSENTPREFERENCECODE]
        ,case when SC.CONSENTCODE = 0 then 'Opt-in'
                else CSC.[CONSENTPREFERENCE] end as [CONSENTPREFERENCE]
        ,CSC.[SOURCECODEID]
        ,CSC.[SOURCEFILEPATH]
        ,CSC.[PRIVACYPOLICYFILEPATH]
        ,CSC.[SUPPORTINGINFORMATION]
        ,CSC.[CONSENTSTATEMENT]
    from [dbo].[CONSTITUENTSOLICITCODE] CSC
    inner join [dbo].[SOLICITCODE] SC
    on CSC.SOLICITCODEID = SC.ID
    where CONSTITUENTID = @ConstituentID and ENDDATE is null