UFN_STEWARDSHIPPLAN_TOPNSTEWARDS_TOITEMLISTXML

create function dbo.UFN_STEWARDSHIPPLAN_TOPNSTEWARDS_TOITEMLISTXML
(
    @PLANID uniqueidentifier,
    @VIEWINACTIVE bit = 0,
    @NUMBERTORETURN smallint = 3
)
returns xml
as
begin

    declare @STEWARDS xml;

    declare @CURRENTDATE datetime;
    set @CURRENTDATE = getdate();

    set @STEWARDS = (
            select top (@NUMBERTORETURN)
                STEWARDSHIPPLANSTEWARD.ID,
                STEWARDSHIPPLANSTEWARD.CONSTITUENTID,
                STEWARDSHIPPLANSTEWARD.ROLECODEID,
                NF.NAME,
                STEWARDSHIPSTEWARDROLECODE.DESCRIPTION as PLANSTEWARDROLECODE
            from
                dbo.STEWARDSHIPPLANSTEWARD
                inner join dbo.CONSTITUENT on STEWARDSHIPPLANSTEWARD.CONSTITUENTID = CONSTITUENT.ID
                left outer join dbo.STEWARDSHIPSTEWARDROLECODE on STEWARDSHIPPLANSTEWARD.ROLECODEID = STEWARDSHIPSTEWARDROLECODE.ID
                outer apply dbo.UFN_CONSTITUENT_DISPLAYNAME(CONSTITUENT.ID) NF
            where
                STEWARDSHIPPLANSTEWARD.PLANID = @PLANID and 
                (
                    @VIEWINACTIVE = 1
                    or
                    (STEWARDSHIPPLANSTEWARD.STARTDATE is null or STEWARDSHIPPLANSTEWARD.STARTDATE <= @CURRENTDATE)
                    and
                    (STEWARDSHIPPLANSTEWARD.ENDDATE is null or @CURRENTDATE <= STEWARDSHIPPLANSTEWARD.ENDDATE)
                )
            order by
                CONSTITUENT.KEYNAME asc, CONSTITUENT.FIRSTNAME asc

            for xml raw('ITEM'), type, elements, root('STEWARDS'), binary base64
        );

        return @STEWARDS;
end