USP_QUERYVIEW_CREATEORUPDATEFUNCTIONRELATIONSHIP

CREATE procedure dbo.[USP_QUERYVIEW_CREATEORUPDATEFUNCTIONRELATIONSHIP]
(
  @ROOTQUERYVIEW nvarchar(128),
  @RELATEDTVF nvarchar(128),
  @ADDEDBYQUERYVIEWID uniqueidentifier,
  @ROOTCOLUMN nvarchar(128),
  @RELATEDCOLUMN nvarchar(128),
  @PATHALIAS nvarchar(128) = null,
  @JOINTYPE tinyint = 0,
  @JOINPARAMETERS nvarchar(255) = null,
  @SEQUENCE int = -1,
  @CHANGEAGENTID uniqueidentifier = null,
  @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  
)
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
        -- this may be a view or a function, so check for both cases

        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
            select @COL = [COLUMN_NAME] from INFORMATION_SCHEMA.ROUTINE_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 ''%s''', 16, 1, @RELATEDCOLUMN, @RELATEDTVF);
                return 1;
              end
          end

        -- validate the function name and column passed in    

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

  if @JOINTYPE <> 2 and @JOINTYPE <> 3
    begin
      raiserror('Invalid join type specified', 16, 1);
      return 3;
    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 = @RELATEDTVF

  if @JOINPARAMETERS is null
    set @JOINPARAMETERS = '0';

  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] = @RELATEDTVF;

  --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],
      [JOINPARAMETERS],
      [SEQUENCE],
      [DISPLAYALIAS],
      [DISPLAYALIASRESOURCEKEY],
      [RESOURCEFILEASSEMBLYNAME],
      [RESOURCEFILECLASSNAME],
      [OBSOLETE],
      [USEFOREXPORTDEFINITIONSONLY],
      [ADDEDBYID],
      [CHANGEDBYID],
      [DATEADDED],
      [DATECHANGED]
    ) values (
      @ROOTQUERYVIEWID,
      @RELATEDQUERYVIEWID,
      @ADDEDBYQUERYVIEWID,
      @ROOTCOLUMN,
      @RELATEDCOLUMN,
      @PATHALIAS,
      @JOINTYPE,
      @JOINPARAMETERS,
      @SEQUENCE,
      @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,
      [DISPLAYALIAS] = @DISPLAYALIAS,
      [DISPLAYALIASRESOURCEKEY] = @DISPLAYALIASRESOURCEKEY,
      [RESOURCEFILEASSEMBLYNAME] = @RESOURCEFILEASSEMBLYNAME,
      [RESOURCEFILECLASSNAME] = @RESOURCEFILECLASSNAME,
      [JOINPARAMETERS] = @JOINPARAMETERS,
      [SEQUENCE] = @SEQUENCE,
      [OBSOLETE] = @OBSOLETE,
      [USEFOREXPORTDEFINITIONSONLY] = @USEFOREXPORTDEFINITIONSONLY,
      [CHANGEDBYID] = @CHANGEAGENTID,
      [DATECHANGED] = @CURRENTDATE
    where ID = @ID;

  return 0;