USP_QUERYVIEW_CLONERELATIONSHIPS

CREATE procedure dbo.[USP_QUERYVIEW_CLONERELATIONSHIPS]
(
  @SOURCEOBJECT nvarchar(128),
  @DESTOBJECT nvarchar(128),
  @ADDEDBYQUERYVIEWID uniqueidentifier,
  @CHANGEAGENTID uniqueidentifier = null,
  @MARTKEY nvarchar(255) = null    
)
as
  set nocount on;

  declare @DESTOBJECTID as uniqueidentifier;
  declare @RELATEDQUERYVIEWID uniqueidentifier;
  declare @PATHALIAS nvarchar(128);
  declare @ROOTCOLUMN nvarchar(128);
  declare @RELATEDCOLUMN nvarchar(128);
  declare @SEQUENCE int;
  declare @JOINTYPE tinyint;
  declare @JOINPARAMETERS nvarchar(255);
  declare @CARDINALITYTYPE tinyint;
  declare @DISPLAYALIAS nvarchar(100);
  declare @DISPLAYALIASRESOURCEKEY nvarchar(100);
  declare @RESOURCEFILEASSEMBLYNAME nvarchar(max);
  declare @RESOURCEFILECLASSNAME nvarchar(max);
  declare @OBSOLETE bit;
  declare @USEFOREXPORTDEFINITIONSONLY bit;
  declare @CURRENTDATE datetime = getdate();

  begin try
    -- default changeagent ID if none is passed in

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

    select
      @DESTOBJECTID = [ID]
    from dbo.[QUERYVIEWCATALOG]
    where [OBJECTNAME] = @DESTOBJECT;

    declare RELATIONSHIPSCURSOR cursor local fast_forward for
      select
        [QUERYVIEWRELATIONSHIP].[RELATEDQUERYVIEWID],
        [QUERYVIEWRELATIONSHIP].[PATHALIAS],
        [QUERYVIEWRELATIONSHIP].[ROOTCOLUMN],
        [QUERYVIEWRELATIONSHIP].[RELATEDCOLUMN],
        [QUERYVIEWRELATIONSHIP].[SEQUENCE],
        [QUERYVIEWRELATIONSHIP].[JOINTYPE],
        [QUERYVIEWRELATIONSHIP].[JOINPARAMETERS],
        [QUERYVIEWRELATIONSHIP].[CARDINALITYTYPE],
        [QUERYVIEWRELATIONSHIP].[DISPLAYALIAS],
        [QUERYVIEWRELATIONSHIP].[DISPLAYALIASRESOURCEKEY],
        [QUERYVIEWRELATIONSHIP].[RESOURCEFILEASSEMBLYNAME],
        [QUERYVIEWRELATIONSHIP].[RESOURCEFILECLASSNAME],
        [QUERYVIEWRELATIONSHIP].[OBSOLETE],
        [QUERYVIEWRELATIONSHIP].[USEFOREXPORTDEFINITIONSONLY]
      from dbo.[QUERYVIEWRELATIONSHIP]
      inner join dbo.[QUERYVIEWCATALOG] on [QUERYVIEWRELATIONSHIP].[ROOTQUERYVIEWID] = [QUERYVIEWCATALOG].[ID]
      where [OBJECTNAME] = @SOURCEOBJECT
      and (
        exists(select * from INFORMATION_SCHEMA.COLUMNS where [COLUMN_NAME] = [QUERYVIEWRELATIONSHIP].[ROOTCOLUMN] and [TABLE_NAME] = @DESTOBJECT)
        or 
        exists(select * from INFORMATION_SCHEMA.ROUTINE_COLUMNS where [COLUMN_NAME] = [QUERYVIEWRELATIONSHIP].[ROOTCOLUMN] and [TABLE_NAME] = @DESTOBJECT)
        or
        @MARTKEY is not null
      );

    open RELATIONSHIPSCURSOR;
    fetch next from RELATIONSHIPSCURSOR into @RELATEDQUERYVIEWID, @PATHALIAS, @ROOTCOLUMN, @RELATEDCOLUMN, @SEQUENCE, @JOINTYPE, @JOINPARAMETERS, @CARDINALITYTYPE, @DISPLAYALIAS, @DISPLAYALIASRESOURCEKEY, @RESOURCEFILEASSEMBLYNAME, @RESOURCEFILECLASSNAME, @OBSOLETE, @USEFOREXPORTDEFINITIONSONLY;

    while (@@fetch_status = 0)
    begin
      if exists(select * from dbo.[QUERYVIEWRELATIONSHIP] where [ROOTQUERYVIEWID] = @DESTOBJECTID and [PATHALIAS] = @PATHALIAS)
        update dbo.[QUERYVIEWRELATIONSHIP] set
          [ROOTQUERYVIEWID] = @DESTOBJECTID,
          [RELATEDQUERYVIEWID] = @RELATEDQUERYVIEWID,
          [ADDEDBYQUERYVIEWID] = @ADDEDBYQUERYVIEWID,
          [PATHALIAS] = @PATHALIAS,
          [ROOTCOLUMN] = @ROOTCOLUMN,
          [RELATEDCOLUMN] = @RELATEDCOLUMN,
          [SEQUENCE] = @SEQUENCE,
          [JOINTYPE] = @JOINTYPE,
          [JOINPARAMETERS] = @JOINPARAMETERS,
          [CARDINALITYTYPE] = @CARDINALITYTYPE,
          [DISPLAYALIAS] = @DISPLAYALIAS,
          [DISPLAYALIASRESOURCEKEY] = @DISPLAYALIASRESOURCEKEY,
          [RESOURCEFILEASSEMBLYNAME] = @RESOURCEFILEASSEMBLYNAME,
          [RESOURCEFILECLASSNAME] = @RESOURCEFILECLASSNAME,
          [OBSOLETE] = @OBSOLETE,
          [USEFOREXPORTDEFINITIONSONLY] = @USEFOREXPORTDEFINITIONSONLY,
          [CHANGEDBYID] = @CHANGEAGENTID,
       [DATECHANGED] = @CURRENTDATE
        where [ROOTQUERYVIEWID] = @DESTOBJECTID
        and [PATHALIAS] = @PATHALIAS;
      else
        insert into dbo.[QUERYVIEWRELATIONSHIP] (
          [ROOTQUERYVIEWID],
          [RELATEDQUERYVIEWID],
          [ADDEDBYQUERYVIEWID],
          [PATHALIAS],
          [ROOTCOLUMN],
          [RELATEDCOLUMN],
          [SEQUENCE],
          [JOINTYPE],
          [JOINPARAMETERS],
          [CARDINALITYTYPE],
          [DISPLAYALIAS],
          [DISPLAYALIASRESOURCEKEY],
          [RESOURCEFILEASSEMBLYNAME],
          [RESOURCEFILECLASSNAME],
          [OBSOLETE],
          [USEFOREXPORTDEFINITIONSONLY],
          [ADDEDBYID],
          [CHANGEDBYID],
          [DATEADDED],
          [DATECHANGED]
        ) values (
          @DESTOBJECTID,
          @RELATEDQUERYVIEWID,
          @DESTOBJECTID,
          @PATHALIAS,
          @ROOTCOLUMN,
          @RELATEDCOLUMN,
          @SEQUENCE,
          @JOINTYPE,
          @JOINPARAMETERS,
          @CARDINALITYTYPE,
          @DISPLAYALIAS,
          @DISPLAYALIASRESOURCEKEY,
          @RESOURCEFILEASSEMBLYNAME,
          @RESOURCEFILECLASSNAME,
          @OBSOLETE,
          @USEFOREXPORTDEFINITIONSONLY,
          @CHANGEAGENTID,
          @CHANGEAGENTID,
          @CURRENTDATE,
          @CURRENTDATE
        );

      fetch next from RELATIONSHIPSCURSOR into @RELATEDQUERYVIEWID, @PATHALIAS, @ROOTCOLUMN, @RELATEDCOLUMN, @SEQUENCE, @JOINTYPE, @JOINPARAMETERS, @CARDINALITYTYPE, @DISPLAYALIAS, @DISPLAYALIASRESOURCEKEY, @RESOURCEFILEASSEMBLYNAME, @RESOURCEFILECLASSNAME, @OBSOLETE, @USEFOREXPORTDEFINITIONSONLY;
    end

    close RELATIONSHIPSCURSOR;
    deallocate RELATIONSHIPSCURSOR;
  end try

  begin catch
    exec dbo.[USP_RAISE_ERROR];
    return 1;
  end catch

  return 0;