USP_RELATIONSHIPSET_RETROCREATE

            CREATE procedure dbo.USP_RELATIONSHIPSET_RETROCREATE
            (
                @RELATIONSHIPSETID uniqueidentifier = null output,
                @CHANGEAGENTID uniqueidentifier = null,    
                @RELATIONSHIPID uniqueidentifier
            ) as
                set nocount on;

                declare @CURRENTDATE datetime;

                if @RELATIONSHIPSETID is null
                    set @RELATIONSHIPSETID = newid();

                if @CHANGEAGENTID is null  
                    exec dbo.USP_CHANGEAGENT_GETORCREATECHANGEAGENT @CHANGEAGENTID output;

                set @CURRENTDATE = getdate();

                begin try

                    --Add relationship set if missing.

                    insert into dbo.RELATIONSHIPSET(ID,ADDEDBYID,CHANGEDBYID)
                    values(@RELATIONSHIPSETID, @CHANGEAGENTID, @CHANGEAGENTID)

                    update
                        dbo.RELATIONSHIP
                    set 
                        RELATIONSHIPSETID = @RELATIONSHIPSETID,
                        CHANGEDBYID = @CHANGEAGENTID,
                        DATECHANGED = @CURRENTDATE
                    where ID = @RELATIONSHIPID;

                    update
                        dbo.RELATIONSHIP
                    set 
                        RELATIONSHIP.RELATIONSHIPSETID = @RELATIONSHIPSETID,
                        RELATIONSHIP.DATECHANGED = @CURRENTDATE,
                        RELATIONSHIP.CHANGEDBYID = @CHANGEAGENTID
                    from
                        RELATIONSHIP
                    inner join
                        RELATIONSHIP ORIG
                    on
                        RELATIONSHIP.RELATIONSHIPCONSTITUENTID = ORIG.RECIPROCALCONSTITUENTID and
                        RELATIONSHIP.RECIPROCALCONSTITUENTID = ORIG.RELATIONSHIPCONSTITUENTID and
                        RELATIONSHIP.RELATIONSHIPTYPECODEID = ORIG.RECIPROCALTYPECODEID and
                        RELATIONSHIP.RECIPROCALTYPECODEID = ORIG.RELATIONSHIPTYPECODEID 
                    where ORIG.ID = @RELATIONSHIPID;

                end try
                begin catch
                    exec dbo.USP_RAISE_ERROR;
                    return 1;
                end catch

                return 0;