USP_CONSTITUENCIES_VALIDATECONSTITUENCIES

            CREATE procedure dbo.USP_CONSTITUENCIES_VALIDATECONSTITUENCIES(
                @CONSTITUENCIES xml
            ) as
                set nocount on;

                declare @CONSTITUENCIESTABLE table
                (
                    ID uniqueidentifier,
                    CONSTITUENCYCODEID uniqueidentifier,
                    DATEFROM datetime,
                    DATETO datetime
                );

                insert into @CONSTITUENCIESTABLE(ID, CONSTITUENCYCODEID, DATEFROM, DATETO)
                    select ID, CONSTITUENCYCODEID, DATEFROM, DATETO
                    from dbo.UFN_CONSTITUENT_GETCONSTITUENCIES_FROMITEMLISTXML(@CONSTITUENCIES);

                -- Validate that constituencies are unique


                declare CONSTITUENCIESCURSOR cursor local fast_forward for
                    select
                        ID,
                        CONSTITUENCYCODEID,
                        DATEFROM,
                        DATETO
                    from
                        @CONSTITUENCIESTABLE;

                declare @ID uniqueidentifier;
                declare @CONSTITUENCYCODEID uniqueidentifier;
                declare @DATEFROM datetime;
                declare @DATETO datetime;
                declare @DUPLICATECOUNT int;

                open CONSTITUENCIESCURSOR;
                    fetch next from CONSTITUENCIESCURSOR into @ID, @CONSTITUENCYCODEID, @DATEFROM, @DATETO;
                    while @@FETCH_STATUS = 0 begin
                        select
                            @DUPLICATECOUNT = count(ID)
                        from
                            @CONSTITUENCIESTABLE
                        where
                            ID <> @ID and
                            CONSTITUENCYCODEID = @CONSTITUENCYCODEID and
                            (
                                ( DATETO   between @DATEFROM and @DATETO) or
                                (@DATETO   between  DATEFROM and  DATETO) or
                                ( DATEFROM between @DATEFROM and @DATETO) or
                                (@DATEFROM between  DATEFROM and  DATETO) or

                                ( DATEFROM is null and @DATEFROM <=  DATETO)   or
                                (@DATEFROM is null and  DATEFROM <= @DATETO)   or
                                ( DATETO   is null and @DATETO   >=  DATEFROM) or
                                (@DATETO   is null and  DATETO   >= @DATEFROM) or

                                (@DATEFROM is null and  DATEFROM is null) or
                                (@DATETO   is null and  DATETO   is null) or
                                (@DATEFROM is null and @DATETO   is null) or
                                ( DATEFROM is null and  DATETO   is null)
                            );

                        if @DUPLICATECOUNT >= 1 begin
                            raiserror('A constituency with the same Constituency type must not have overlapping dates.', 13, 1)
                            return 1;
                        end

                        fetch next from CONSTITUENCIESCURSOR into @ID, @CONSTITUENCYCODEID, @DATEFROM, @DATETO;
                    end

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

                close CONSTITUENCIESCURSOR;
                deallocate CONSTITUENCIESCURSOR;

                return 0;