USP_QUERYVIEW_DELETERELATIONSHIP

create procedure [dbo].[USP_QUERYVIEW_DELETERELATIONSHIP]
    @OBJECTNAME [nvarchar](128),
    @RELATEDOBJECT [nvarchar](128),
    @PATHALIAS [nvarchar](128) = null,
    @CHANGEAGENTID [uniqueidentifier] = null
with execute as caller
as
set NOCOUNT on;

declare @ROOTQUERYVIEWID uniqueidentifier
declare @RELATEDQUERYVIEWID uniqueidentifier
declare @ID uniqueidentifier

-- validate the object names passed in
if @OBJECTNAME is null or @OBJECTNAME = ''
    begin
        raiserror ('ObjectName must be specified', 16,1)
        return 1
    end

if @RELATEDOBJECT is null or @RELATEDOBJECT = ''
    begin
        raiserror ('RelatedObject must be specified', 16,1)
        return 2
    end

if @PATHALIAS is null
    set @PATHALIAS = ''

select @ROOTQUERYVIEWID = ID from dbo.QUERYVIEWCATALOG
    where OBJECTNAME = @OBJECTNAME

select @RELATEDQUERYVIEWID = ID from dbo.QUERYVIEWCATALOG
    where OBJECTNAME = @RELATEDOBJECT

select @ID = ID from dbo.QUERYVIEWRELATIONSHIP 
    where ROOTQUERYVIEWID = @ROOTQUERYVIEWID and RELATEDQUERYVIEWID = @RELATEDQUERYVIEWID 
     and (@PATHALIAS = '' or  PATHALIAS = @PATHALIAS)

declare @r int

if not @ID is null
    begin
        exec @r= dbo.USP_QUERYVIEWRELATIONSHIP_DELETEBYID_WITHCHANGEAGENTID @ID, @CHANGEAGENTID
        if @r <> 0 return @r;
    end