UFN_VSECONSTITUENCY_CONSTITUENCYLIST2

CREATE function dbo.UFN_VSECONSTITUENCY_CONSTITUENCYLIST2
(
    @VSECONSTITUENCYID uniqueidentifier,
    @INCLUDEINACTIVE bit = 0
)
returns nvarchar(3000)
as begin
    declare @CONSTITUENCY nvarchar(512);
    declare @ISACTIVE bit;
    declare @r nvarchar(3000);
    declare @SEPERATOR nvarchar(2);

    set @r=N'';

    declare CONSTITUENCYCURSOR cursor local fast_forward for
        select
            dbo.UFN_CONSTITUENCY_GETDESCRIPTION(VSECONSTITUENCYMAP.CONSTITUENCYID) as CONSTITUENCY,
            dbo.CONSTITUENCYDEFINITION.ISACTIVE as ISACTIVE
        from
            dbo.VSECONSTITUENCYMAP
            inner join dbo.CONSTITUENCYDEFINITION
                on CONSTITUENCYDEFINITION.ID = VSECONSTITUENCYMAP.CONSTITUENCYID
        where
            VSECONSTITUENCYMAP.VSECONSTITUENCYID = @VSECONSTITUENCYID
            and (@INCLUDEINACTIVE = 1 or CONSTITUENCYDEFINITION.ISACTIVE = 1);

    open CONSTITUENCYCURSOR;
        fetch next from CONSTITUENCYCURSOR into @CONSTITUENCY, @ISACTIVE;
        set @r = '';
        set @SEPERATOR = '';

        while @@FETCH_STATUS = 0
        begin
            set @r = @r + @SEPERATOR + @CONSTITUENCY;
            if @ISACTIVE = 0
            begin
                set @r = @r + ' (inactive)';
            end
            set @SEPERATOR = '; ';
            fetch next from CONSTITUENCYCURSOR into @CONSTITUENCY, @ISACTIVE;
        end

    --When a cursor is used, it should be explicitly closed/deallocated in case of blocking or USP running long

    close CONSTITUENCYCURSOR;
    deallocate CONSTITUENCYCURSOR;

    return @r;
end