USP_GET_DONORINFORMATIONFIELDS_BY_CONTENTID

create procedure dbo.USP_GET_DONORINFORMATIONFIELDS_BY_CONTENTID
(
        @CONTENTID int = 0
)
as

    if (@ContentID !=  0) and exists(select 1 from DONORINFORMATIONFIELDS where CONTENTID =  @CONTENTID)

        select
                    ID,
                    CONTENTID, 
                    FIELDID, 
                    FIELDNAME,
                    SEQUENCE,
                    DISPLAYTEXT,
                    ISINCLUDED, 
                    ISREQUIRED,
                    ROWTYPECODE,
          CODETABLEID
    from dbo.DONORINFORMATIONFIELDS
        where CONTENTID = @ContentID AND FIELDID NOT IN (6, 75, 52, 45)
        order by SEQUENCE 

    else
    declare @DEFAULTINCLUDED as bit = 0;
    declare @PERSONALINFOTYPE as tinyint  = 0

        select
                    null as ID,
                    null as    CONTENTID,
                    a.[FieldID] as FIELDID,
          Case a.[FieldID] When 23 Then 'Email Address' Else a.[FieldName] end as FIELDNAME,
                    null as SEQUENCE,
                    Case a.[FieldID] When 23 Then 'Email Address' Else a.[FieldName] end as DISPLAYTEXT, 
                    @DEFAULTINCLUDED as ISINCLUDED,
                    @DEFAULTINCLUDED as ISREQUIRED,
                    @PERSONALINFOTYPE as ROWTYPECODE,
          CODETABLEID
        from applicationfields as a  

        -- field id for selected fields i.e field id for First Name- 2, Middle Name -3, LastName - 4, Title1 -5,Maiden Name- 8, Home Phone - 21, Preferred Email - 23, School Name - 45, 

        -- Class Of - 52, Formatted Address- 116, Confirm Email Address - 1020 Suffix 1 - 6, Suffix 2 - 75'

        where  a.FIELDID in (5,2,3,4,8,23,1020,116,21)
            order by  case FIELDNAME 
                 when  'Title 1' then 1 --Title1 

                 when  'First Name' then 2 --FirstName

                 when  'Middle Name' then 3 -- Middle Name

                 when  'Last Name' then 4 -- Last Name

                 when  'Maiden Name' then 7 -- Maiden Name

                 when  'Preferred Email' then 8 -- Email Address

                 when  'Confirm Email Address' then 9 -- Confirm Email 

                 when  'Formatted Address' then 10 -- Address

                 when  'Home Phone' then 11 -- Home Phone


    end