USP_GET_APPEAL_AND_MAPID

        create  procedure dbo.USP_GET_APPEAL_AND_MAPID
        ( @MAXROWS int =-1,@NAME as nvarchar(100) ='',@EVENTIDS as nvarchar(2000) ='',@RECORDTYPE int =0,@Page int =1,@CATEGORYID as nvarchar(40) =null,@DESCRIPTION as nvarchar(100)='')
        as
        begin
            if @MAXROWS >=0 
            begin
                declare @Offset int = (@Page * @MAXROWS) - @MAXROWS;
                if(@RECORDTYPE =0)
                begin

                set @EVENTIDS =','+@EVENTIDS+','
                    select 
                        A.ID APPEALID,
                        A.NAME as APPEALNAME,
                        A.DESCRIPTION DESCRIPTION,
                        dbo.UFN_APPEALCATEGORYCODE_GETDESCRIPTION(A.APPEALCATEGORYCODEID) APPEALCATEGORY,
                        A.ISACTIVE ISACTIVE,
                        COALESCE((SELECT NAME FROM dbo.SITE WHERE ID = A.SITEID),'''') AS SITE,
                        coalesce(E.NAME, dbo.UFN_EVENT_GETNAME(EA.EVENTID)) as EVENTNAME,
                        BA.ID as CMSAPPEALID,
                        count(1) over() as TOTALRECORDS
                    from appeal as A
                    inner join dbo.EVENTAPPEAL as EA on EA.APPEALID = A.ID
                    inner join dbo.BBNCAPPEALIDMAP as BA on BA.APPEALID =A.ID
                    inner join dbo.BBNCEVENTIDMAP as BE on BE.EVENTID =EA.EVENTID
                    inner join dbo.Event as E on E.ID =BE.EVENTID
                    where 
                        Charindex(','+cast(BE.ID as varchar)+',', @EVENTIDS) >0
                         and (@NAME ='' or A.NAME like '%'+@NAME+'%')
                        and A.ISACTIVE = 1 
                        and (@CATEGORYID is null or @CATEGORYID =A.APPEALCATEGORYCODEID)
                        and (@DESCRIPTION ='' or A.DESCRIPTION like '%'+@DESCRIPTION+'%' )
                        ORDER BY A.NAME
                    offset @Offset rows
                    fetch next @MAXROWS rows only
                end
                else
                begin
                    SELECT  
                            APPEAL.ID APPEALID, 
                            APPEAL.NAME as APPEALNAME,
                            APPEAL.DESCRIPTION DESCRIPTION,
                            dbo.UFN_APPEALCATEGORYCODE_GETDESCRIPTION(APPEAL.APPEALCATEGORYCODEID) APPEALCATEGORY,
                            APPEAL.ISACTIVE ISACTIVE, COALESCE((SELECT NAME FROM dbo.SITE WHERE ID = APPEAL.SITEID),'''') AS SITE,
                            coalesce(EVENT.NAME, dbo.UFN_EVENT_GETNAME(EVENTAPPEAL.EVENTID)) as EVENTNAME,
                     BBNCAPPEALIDMAP.ID as CMSAPPEALID,
                    count(1) over() as TOTALRECORDS
                     FROM dbo.APPEAL 
                     left join dbo.EVENT on EVENT.APPEALID = APPEAL.ID 
                     left join dbo.EVENTAPPEAL on APPEAL.ID = EVENTAPPEAL.APPEALID 
                     left outer join dbo.BBNCAPPEALIDMAP on APPEAL.ID = BBNCAPPEALIDMAP.APPEALID
                     WHERE
                    (@NAME ='' or APPEAL.NAME like '%'+@NAME+'%')
                     and APPEAL.ISACTIVE = 1
                     and (@CATEGORYID is null or @CATEGORYID =APPEAL.APPEALCATEGORYCODEID)
                     and (@DESCRIPTION ='' or APPEAL.DESCRIPTION like '%'+@DESCRIPTION+'%' )
                     ORDER BY APPEAL.NAME
                     offset @Offset rows
                    fetch next @MAXROWS rows only
                 end
             end
             else
             begin
                SELECT  
                    APPEAL.ID,
                    APPEAL.NAME as APPEALNAME,
                    APPEAL.DESCRIPTION,
                    dbo.UFN_APPEALCATEGORYCODE_GETDESCRIPTION(APPEAL.APPEALCATEGORYCODEID),
                    APPEAL.ISACTIVE,
                    COALESCE((SELECT NAME FROM dbo.SITE WHERE ID = APPEAL.SITEID),'''') AS SITE,
                    coalesce(EVENT.NAME, dbo.UFN_EVENT_GETNAME(EVENTAPPEAL.EVENTID)) as EVENTNAME,
                 BBNCAPPEALIDMAP.ID as CMSAPPEALID
                 FROM dbo.APPEAL 
                 left join dbo.EVENT on EVENT.APPEALID = APPEAL.ID 
                 left join dbo.EVENTAPPEAL on APPEAL.ID = EVENTAPPEAL.APPEALID 
                 left outer join dbo.BBNCAPPEALIDMAP on APPEAL.ID = BBNCAPPEALIDMAP.APPEALID
                 WHERE
                (@NAME ='' or APPEAL.NAME like '%'+@NAME+'%')
                 and APPEAL.ISACTIVE = 1
                 and (@CATEGORYID is null or @CATEGORYID =APPEAL.APPEALCATEGORYCODEID)
                 and (@DESCRIPTION ='' or APPEAL.DESCRIPTION like '%'+@DESCRIPTION+'%' )
                 ORDER BY APPEAL.NAME
             End 
        end