USP_CONSTITUENT_GETCONSECUTIVEYEARSFROMGIVENSET

        CREATE procedure dbo.USP_CONSTITUENT_GETCONSECUTIVEYEARSFROMGIVENSET(
                @SETSQL nvarchar(max),
                @THISYEAR datetime,
                @LASTYEAR datetime,
                @CONSECUTIVEYEARS int = null output,
                @GIVENSINCEFISCALYEAR datetime = null output
        )
        as
        begin
                set nocount on

                --This is a fairly long calculation that should not be duplicated however it requires you know the list of revenue

                --Using SP_EXECUTESQL here to allow consolidation of code but allow for any table to be used. ID column must be a REVENUESPLITID



                declare @SQL nvarchar(max);

                set @SQL = replace('
                declare @CURRENTDATE datetime;
                declare @FISCALYEAR_FIRSTDAY datetime;
                declare @PREVIOUSFISCALYEAR_FIRSTDAY datetime;
                declare @CONTINUE bit;

                set @CURRENTDATE = getdate();

                declare FISCALYEARCURSOR cursor local fast_forward for
                <SETSQL>

                set @CONSECUTIVEYEARS = 0;
                set @GIVENSINCEFISCALYEAR = null;

                open FISCALYEARCURSOR;
                    fetch next from FISCALYEARCURSOR into @FISCALYEAR_FIRSTDAY;
                    if @@FETCH_STATUS = 0 begin
                        if @FISCALYEAR_FIRSTDAY > @THISYEAR begin
                            set @CONTINUE = 1;
                            fetch next from FISCALYEARCURSOR into @PREVIOUSFISCALYEAR_FIRSTDAY;
                        end
                        else if @FISCALYEAR_FIRSTDAY = @THISYEAR or
                            @FISCALYEAR_FIRSTDAY = @LASTYEAR begin
                            set @CONSECUTIVEYEARS = @CONSECUTIVEYEARS + 1;
                            set @CONTINUE = 1;
                            fetch next from FISCALYEARCURSOR into @PREVIOUSFISCALYEAR_FIRSTDAY;
                        end
                        else
                            set @CONTINUE = 0;
                    end

                    while @@FETCH_STATUS = 0 and @CONTINUE = 1 begin
                        if @CONSECUTIVEYEARS = 0 begin
                            if @PREVIOUSFISCALYEAR_FIRSTDAY > @THISYEAR begin
                                set @FISCALYEAR_FIRSTDAY = @PREVIOUSFISCALYEAR_FIRSTDAY;
                                fetch next from FISCALYEARCURSOR into @PREVIOUSFISCALYEAR_FIRSTDAY;
                            end
                            else if @PREVIOUSFISCALYEAR_FIRSTDAY = @THISYEAR or
                                @PREVIOUSFISCALYEAR_FIRSTDAY = @LASTYEAR begin
                                set @CONSECUTIVEYEARS = @CONSECUTIVEYEARS + 1;
                                set @FISCALYEAR_FIRSTDAY = @PREVIOUSFISCALYEAR_FIRSTDAY;
                                fetch next from FISCALYEARCURSOR into @PREVIOUSFISCALYEAR_FIRSTDAY;
                            end
                            else
                                set @CONTINUE = 0
                        end
                        else if @PREVIOUSFISCALYEAR_FIRSTDAY = dateadd(year, -1, @FISCALYEAR_FIRSTDAY) begin
                            set @CONSECUTIVEYEARS = @CONSECUTIVEYEARS + 1;
                            set @FISCALYEAR_FIRSTDAY = @PREVIOUSFISCALYEAR_FIRSTDAY;
                            fetch next from FISCALYEARCURSOR into @PREVIOUSFISCALYEAR_FIRSTDAY;
                        end
                        else
                            set @CONTINUE = 0;
                    end

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

                close FISCALYEARCURSOR;
                deallocate FISCALYEARCURSOR;

                if @CONSECUTIVEYEARS > 0
                    set @GIVENSINCEFISCALYEAR = @FISCALYEAR_FIRSTDAY;

                ', '<SETSQL>', @SETSQL);

                exec sp_executesql @SQL, N'@CONSECUTIVEYEARS int = null output,@GIVENSINCEFISCALYEAR datetime = null output, @THISYEAR datetime = null, @LASTYEAR datetime = null', 
                    @CONSECUTIVEYEARS = @CONSECUTIVEYEARS output,@GIVENSINCEFISCALYEAR = @GIVENSINCEFISCALYEAR output, @THISYEAR = @THISYEAR, @LASTYEAR = @LASTYEAR;


        end