USP_DATALIST_PROGRAMDOCUMENT

CREATE procedure dbo.USP_DATALIST_PROGRAMDOCUMENT
(
    @PROGRAMID uniqueidentifier,
    @SHOWINACTIVE bit = 0
)
as
    set nocount on;

    select PD.ID,
           D.ID as DOCUMENTID,
           (PD.SEQUENCE + 1) as PRIORITY,
           D.NAME,
           D.DESCRIPTION,
           D.ISACTIVE,
           D.REPORTCATALOGID,
           (select max(SEQUENCE) + 1 from dbo.PROGRAMDOCUMENT where PROGRAMID = @PROGRAMID) as MAXPRIORITY
    from dbo.DOCUMENT as D
        inner join dbo.PROGRAMDOCUMENT as PD on PD.DOCUMENTID = D.ID
    where
        PD.PROGRAMID = @PROGRAMID and
        (@SHOWINACTIVE = 1 or D.ISACTIVE = 1)
    order by PD.SEQUENCE asc