USP_EVENTPREFERENCE_VALIDATEPREFERENCES

            CREATE procedure dbo.USP_EVENTPREFERENCE_VALIDATEPREFERENCES(@PREFERENCES xml, @PREFERENCEGROUPID uniqueidentifier = null)
            with execute as caller
            as            
                declare @NAME nvarchar(100)
                declare @ERROR nvarchar(200)
                declare @PREFERENCESTABLE table
                        (
                            ID uniqueidentifier null,
                            NAME nvarchar(100)
                        )

                --insert into @PREFERENCESTABLE(ID, NAME) select ROW_NUMBER() OVER (Order by ID, SEQUENCE), NAME from dbo.UFN_EVENTPREFERENCE_GETPREFERENCES_FROMITEMLISTXML(@PREFERENCES);

                insert into @PREFERENCESTABLE(ID, NAME) select ID, NAME from dbo.UFN_EVENTPREFERENCE_GETPREFERENCES_FROMITEMLISTXML(@PREFERENCES);                                                                

                set @NAME = '';

                if @PREFERENCEGROUPID is not null begin
                    select top 1
                        @NAME = EVENTPREFERENCE.NAME
                    from 
                        dbo.EVENTPREFERENCE
                        inner join dbo.REGISTRANTPREFERENCE on REGISTRANTPREFERENCE.EVENTPREFERENCEID = EVENTPREFERENCE.ID
                    where
                        not exists(select 
                                        EDITTED.ID 
                                    from 
                                        @PREFERENCESTABLE as EDITTED
                                    where
                                        EDITTED.ID = EVENTPREFERENCE.ID and coalesce(EDITTED.NAME,'') <> '')
                        and
                        EVENTPREFERENCE.EVENTPREFERENCEGROUPID = @PREFERENCEGROUPID
                    order by
                        EVENTPREFERENCE.ID;

                    if @NAME <> '' begin
                        set @ERROR = @NAME + ' cannot be deleted because it exists on a registrant record.';
                        raiserror(@ERROR, 13, 1);
                    end;                    
                end

                set @NAME = '';

                select 
                    top 1 @NAME = NAME
                from 
                    @PREFERENCESTABLE as PREFERENCES 
                group by 
                    NAME
                having 
                    count(NAME) > 1;

                if @NAME <> '' begin
                    set @ERROR = @NAME + ' is listed more than once.';
                    raiserror(@ERROR, 13, 1);
                end;