USP_QUERYVIEW_CREATEORUPDATEVIEWRELATIONSHIP

CREATE procedure dbo.[USP_QUERYVIEW_CREATEORUPDATEVIEWRELATIONSHIP]
(
  @ROOTQUERYVIEW nvarchar(128),
  @RELATEDQUERYVIEW nvarchar(128),
  @ADDEDBYQUERYVIEWID uniqueidentifier,
  @ROOTCOLUMN nvarchar(128),
  @RELATEDCOLUMN nvarchar(128),
  @PATHALIAS nvarchar(128) = null,
  @JOINTYPE tinyint = 0,
  @SEQUENCE int = -1,
  @CHANGEAGENTID uniqueidentifier = null,
  @CARDINALITYTYPE tinyint = 0,
  @DISPLAYALIAS nvarchar(100) = null,
  @DISPLAYALIASRESOURCEKEY nvarchar(100) = null,
  @RESOURCEFILEASSEMBLYNAME nvarchar(max) = null,
  @RESOURCEFILECLASSNAME nvarchar(max) = null,
  @OBSOLETE bit = 0,
  @USEFOREXPORTDEFINITIONSONLY bit = 0,
  @MARTKEY nvarchar(255) = null,
  @SUBGROUP nvarchar(255) = null,
  @SUBGROUPRESOURCEKEY nvarchar(100) = null
)
with execute as caller
as
  set nocount on;

  declare @ID as uniqueidentifier;
  declare @COL as nvarchar(128);
  declare @CURRENTSEQUENCE as int;
  declare @ROOTQUERYVIEWID uniqueidentifier;
  declare @RELATEDQUERYVIEWID uniqueidentifier;


  if @MARTKEY is null

    begin
      -- validate the view names and columns passed in

      select @COL = [COLUMN_NAME] from INFORMATION_SCHEMA.COLUMNS where [TABLE_SCHEMA] = 'dbo' and [TABLE_NAME] = @ROOTQUERYVIEW and [COLUMN_NAME] = @ROOTCOLUMN;
      if @COL is null
        begin
          raiserror('Column ''%s'' does not exist in view ''%s''', 16, 1, @ROOTCOLUMN, @ROOTQUERYVIEW);
          return 1;
        end

      select @COL = [COLUMN_NAME] from INFORMATION_SCHEMA.COLUMNS where [TABLE_SCHEMA] = 'dbo' and [TABLE_NAME] = @RELATEDQUERYVIEW and [COLUMN_NAME] = @RELATEDCOLUMN;
      if @COL is null
        begin
          raiserror('Column ''%s'' does not exist in view ''%s''', 16, 1, @RELATEDCOLUMN, @RELATEDQUERYVIEW);
          return 2;
        end

      if @JOINTYPE <> 0 and @JOINTYPE <> 1
        begin
          raiserror('Invalid join type specified', 16, 1);
          return 3;
        end
    end

  -- default changeagent ID if none is passed in

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

  if @PATHALIAS is null or len(@PATHALIAS) = 0
    set @PATHALIAS = @RELATEDQUERYVIEW;

  if @SUBGROUP is null
    set @SUBGROUP = '';

  if @SUBGROUPRESOURCEKEY is null
    set @SUBGROUPRESOURCEKEY = '';

if @DISPLAYALIAS is null
    set @DISPLAYALIAS = '';

  if @DISPLAYALIASRESOURCEKEY is null
    set @DISPLAYALIASRESOURCEKEY = '';

  if @RESOURCEFILEASSEMBLYNAME is null
    set @RESOURCEFILEASSEMBLYNAME = '';

  if @RESOURCEFILECLASSNAME is null
    set @RESOURCEFILECLASSNAME = '';

  select
    @ROOTQUERYVIEWID = [ID]
  from dbo.[QUERYVIEWCATALOG]
  where [OBJECTNAME] = @ROOTQUERYVIEW;

  select
    @RELATEDQUERYVIEWID = [ID]
  from dbo.[QUERYVIEWCATALOG]
  where [OBJECTNAME] = @RELATEDQUERYVIEW;

  --retrieve ID if it already exists

  select
    @ID = [ID],
    @CURRENTSEQUENCE = [SEQUENCE]
  from dbo.[QUERYVIEWRELATIONSHIP]
  where [ROOTQUERYVIEWID] = @ROOTQUERYVIEWID
  and [RELATEDQUERYVIEWID] = @RELATEDQUERYVIEWID
  and [PATHALIAS] = @PATHALIAS;

  if @SEQUENCE is null or @SEQUENCE = -1
    begin
      if @ID is null
        select
          @SEQUENCE = isnull(max([SEQUENCE]), -1) + 1
        from dbo.[QUERYVIEWRELATIONSHIP]
        where [ROOTQUERYVIEWID] = @ROOTQUERYVIEWID;
      else
        set @SEQUENCE = @CURRENTSEQUENCE;
    end

  declare @CURRENTDATE datetime = getdate();

  if @ID is null
    insert into dbo.[QUERYVIEWRELATIONSHIP] (
      [ROOTQUERYVIEWID],
      [RELATEDQUERYVIEWID],
      [ADDEDBYQUERYVIEWID],
      [ROOTCOLUMN],
      [RELATEDCOLUMN],
      [PATHALIAS],
      [JOINTYPE],
      [SEQUENCE],
      [CARDINALITYTYPE],
      [SUBGROUP],
      [SUBGROUPRESOURCEKEY],
      [DISPLAYALIAS],
      [DISPLAYALIASRESOURCEKEY],
      [RESOURCEFILEASSEMBLYNAME],
      [RESOURCEFILECLASSNAME],
      [OBSOLETE],
      [USEFOREXPORTDEFINITIONSONLY],
      [ADDEDBYID],
      [CHANGEDBYID],
      [DATEADDED],
      [DATECHANGED]
    ) values (
      @ROOTQUERYVIEWID,
      @RELATEDQUERYVIEWID,
      @ADDEDBYQUERYVIEWID,
      @ROOTCOLUMN,
      @RELATEDCOLUMN,
      @PATHALIAS,
      @JOINTYPE,
      @SEQUENCE,
      @CARDINALITYTYPE,
      @SUBGROUP,
      @SUBGROUPRESOURCEKEY,
      @DISPLAYALIAS,
      @DISPLAYALIASRESOURCEKEY,
      @RESOURCEFILEASSEMBLYNAME,
      @RESOURCEFILECLASSNAME,
      @OBSOLETE,
      @USEFOREXPORTDEFINITIONSONLY,
      @CHANGEAGENTID,
      @CHANGEAGENTID,
      @CURRENTDATE,
      @CURRENTDATE
    );
  else
    update dbo.[QUERYVIEWRELATIONSHIP] set 
      [ROOTQUERYVIEWID] = @ROOTQUERYVIEWID,
      [RELATEDQUERYVIEWID] = @RELATEDQUERYVIEWID,
      [ADDEDBYQUERYVIEWID] = @ADDEDBYQUERYVIEWID,
      [ROOTCOLUMN] = @ROOTCOLUMN,
      [RELATEDCOLUMN] = @RELATEDCOLUMN,
      [PATHALIAS] = @PATHALIAS,
      [JOINTYPE] = @JOINTYPE,
      [SUBGROUP] = @SUBGROUP,
      [SUBGROUPRESOURCEKEY] = @SUBGROUPRESOURCEKEY,
      [DISPLAYALIAS] = @DISPLAYALIAS,
      [DISPLAYALIASRESOURCEKEY] = @DISPLAYALIASRESOURCEKEY,
      [RESOURCEFILEASSEMBLYNAME] = @RESOURCEFILEASSEMBLYNAME,
      [RESOURCEFILECLASSNAME] = @RESOURCEFILECLASSNAME,
      [SEQUENCE] = @SEQUENCE,
      [CARDINALITYTYPE] = @CARDINALITYTYPE,
      [OBSOLETE] = @OBSOLETE,
      [USEFOREXPORTDEFINITIONSONLY] = @USEFOREXPORTDEFINITIONSONLY,
      [CHANGEDBYID] = @CHANGEAGENTID,
      [DATECHANGED] = @CURRENTDATE
    where [ID] = @ID;

  return 0;