USP_ADHOCQUERYDEPENDENCY_GENERATEDEPENDENCIES

CREATE  procedure USP_ADHOCQUERYDEPENDENCY_GENERATEDEPENDENCIES 
  (
  @ADHOCQUERYID uniqueidentifier, 
  @IDSETREGISTERID uniqueidentifier,
  @CHANGEAGENTID uniqueidentifier=null
  )
with execute as owner
as
  if @CHANGEAGENTID is null
    exec dbo.USP_CHANGEAGENT_GETORCREATECHANGEAGENT @CHANGEAGENTID output;

  declare @CURRENTDATE datetime; 
    set @CURRENTDATE=GetDate();

  --clear the current values for this query in the dependencies table

  delete from dbo.ADHOCQUERYDEPENDENCY where dbo.ADHOCQUERYDEPENDENCY.ADHOCQUERYID=@ADHOCQUERYID;

  declare @TABLENAME as nvarchar(100);
  declare @DBTABLENAME as nvarchar(100);
  declare @FOREIGNKEYCOLUMNNAME as nvarchar(100);
  declare @TABLEDESCRIPTION as nvarchar(255);
  declare @RECORDTYPEID as uniqueidentifier;
  declare @DELETEREFERENTIALACTIONDESCRIPTION as nvarchar(255);
  declare @FOREIGNKEYNAME as nvarchar(255);

  declare @SQLTEXT as nvarchar(2000);

  --possible_tables cursor calculates all of the foreign key relationships in the db between the tables, this includes custom user ones

  declare POSSIBLETABLES_CURSOR cursor for 
    with xmlnamespaces ('bb_appfx_table' as x)  
      select 
        (select TABLECATALOG.TABLESPECXML.value('(/x:TableSpec/@Name)[1]', 'nvarchar(max)')) as TABLENAME,  --This table name is also the record type

        TABLECATALOG.DESCRIPTION  as TABLEDESCRIPTION,
        SYSTABLES.name as DBTABLENAME, 
        SYSTEMCOLUMNS.name as FOREIGNKEYCOLUMNNAME,
        RECORDTYPE.ID as RECORDTYPEID,
        FOREIGNKEYS.delete_referential_action_desc as DELETEREFERENTIALACTIONDESCRIPTION,
        FOREIGNKEYS.name as FOREIGNKEYNAME
      from sys.foreign_keys AS FOREIGNKEYS 
        inner join sys.foreign_key_columns as FOREIGNKEYCOLUMNS ON FOREIGNKEYS.OBJECT_ID = FOREIGNKEYCOLUMNS.constraint_object_id
        inner join sys.tables as SYSTABLES on FOREIGNKEYCOLUMNS.parent_object_id = SYSTABLES.object_id 
      inner join sys.columns as SYSTEMCOLUMNS on FOREIGNKEYCOLUMNS.parent_object_id = SYSTEMCOLUMNS.object_id and FOREIGNKEYCOLUMNS.parent_column_id = SYSTEMCOLUMNS.column_id
        inner join dbo.TABLECATALOG on TABLECATALOG.TABLENAME=SYSTABLES.name
        inner join dbo.RECORDTYPE on RECORDTYPE.BASETABLENAME=SYSTABLES.name
      where OBJECT_NAME (FOREIGNKEYCOLUMNS.referenced_object_id) in ('adhocquery', 'IDSETREGISTER') 
        and SYSTABLES.name <> 'ADHOCQUERYDEPENDENCY'  -- excluding the entries for this dependency process and associated table

        and SYSTABLES.name <> 'ADHOCQUERYDEPENDENCYBUSINESSPROCESS';

  open POSSIBLETABLES_CURSOR;  
  fetch next from POSSIBLETABLES_CURSOR into 
    @TABLENAME, 
    @TABLEDESCRIPTION, 
    @DBTABLENAME, 
    @FOREIGNKEYCOLUMNNAME, 
    @RECORDTYPEID, 
    @DELETEREFERENTIALACTIONDESCRIPTION, 
    @FOREIGNKEYNAME; 

  while @@FETCH_STATUS = 0  
    begin  
      set @SQLTEXT='declare @ADHOCQUERYID uniqueidentifier; set @ADHOCQUERYID='''+ CAST(@ADHOCQUERYID AS VARCHAR(36)) + '''; ';
      set @SQLTEXT=@SQLTEXT+'declare @IDSETREGISTERID uniqueidentifier; set @IDSETREGISTERID='''+ CAST(@IDSETREGISTERID AS VARCHAR(36)) + '''; ';
      set @SQLTEXT=@SQLTEXT+'declare @CHANGEAGENTID uniqueidentifier; set @CHANGEAGENTID='''+ CAST(@CHANGEAGENTID AS VARCHAR(36)) + '''; ';
      set @SQLTEXT=@SQLTEXT+'declare @CURRENTDATE datetime; set @CURRENTDATE=GetDate(); ';

      -- to handle strings that contain a quotation mark correctly

      set @SQLTEXT=@SQLTEXT+'declare @TABLENAME nvarchar(200); set @TABLENAME='''+ replace(@TABLENAME, '''', '''''') +'''; ';
      set @SQLTEXT=@SQLTEXT+'declare @TABLEDESCRIPTION nvarchar(510); set @TABLEDESCRIPTION='''+ replace(@TABLEDESCRIPTION, '''', '''''') +'''; ';

     --insert a row for each dependency

      set @SQLTEXT= @SQLTEXT + 'insert into dbo.ADHOCQUERYDEPENDENCY ';
      set @SQLTEXT= @SQLTEXT + '(ADHOCQUERYID, IDSETREGISTERID, TABLENAME, TABLEDESCRIPTION, DBTABLENAME, RECORDTYPEID, RECORDID, DELETEREFERENTIALACTIONDESCRIPTION, FOREIGNKEYNAME, ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED) ';

      if @IDSETREGISTERID='00000000-0000-0000-0000-000000000000'
        begin
          set @SQLTEXT= @SQLTEXT + '(select @ADHOCQUERYID, null, @TABLENAME, @TABLEDESCRIPTION, '''+@DBTABLENAME+''', '''+  CAST(@RECORDTYPEID AS VARCHAR(36))+ ''', ' +@DBTABLENAME + '.ID, '''+  @DELETEREFERENTIALACTIONDESCRIPTION+ ''', '''+  @FOREIGNKEYNAME+ ''', @CHANGEAGENTID, @CHANGEAGENTID, @CURRENTDATE, @CURRENTDATE ';
          set @SQLTEXT= @SQLTEXT + 'from ' + @DBTABLENAME + ' where ' + @FOREIGNKEYCOLUMNNAME + ' =@ADHOCQUERYID);';
        end
      else 
      begin
         set @SQLTEXT= @SQLTEXT + '(select @ADHOCQUERYID, @IDSETREGISTERID, @TABLENAME, @TABLEDESCRIPTION, '''+@DBTABLENAME+''', '''+  CAST(@RECORDTYPEID AS VARCHAR(36))+ ''', ' +@DBTABLENAME + '.ID, '''+  @DELETEREFERENTIALACTIONDESCRIPTION+ ''', '''+  @FOREIGNKEYNAME+ ''', @CHANGEAGENTID, @CHANGEAGENTID, @CURRENTDATE, @CURRENTDATE ';
         set @SQLTEXT= @SQLTEXT + 'from ' + @DBTABLENAME + ' where ' + @FOREIGNKEYCOLUMNNAME + ' =@ADHOCQUERYID or ' + @FOREIGNKEYCOLUMNNAME + '=@IDSETREGISTERID) ;';
      end;

      exec ( @SQLTEXT);
    fetch next from POSSIBLETABLES_CURSOR into @TABLENAME, @TABLEDESCRIPTION, @DBTABLENAME, @FOREIGNKEYCOLUMNNAME, @RECORDTYPEID, @DELETEREFERENTIALACTIONDESCRIPTION, @FOREIGNKEYNAME; 
   end;  
  close POSSIBLETABLES_CURSOR;  
  deallocate POSSIBLETABLES_CURSOR;

  -- check other dependencies that don't have FK constraint to the current query selection

  if @IDSETREGISTERID <> '00000000-0000-0000-0000-000000000000'
     begin
        --this handles dependencies to smart query, which don't have a FK constraint


        --get the recordId for SmartQueryInstance

        declare @RECORDTYPEIDSMARTQUERYINSTANCE uniqueidentifier;
        set @RECORDTYPEIDSMARTQUERYINSTANCE=(select ID from dbo.RECORDTYPE where BASETABLENAME='SMARTQUERYINSTANCE');

        insert into dbo.ADHOCQUERYDEPENDENCY 
            (ADHOCQUERYID, 
            IDSETREGISTERID, 
            TABLENAME, 
            TABLEDESCRIPTION, 
            DBTABLENAME, 
            RECORDTYPEID, 
            RECORDID, 
            ADDEDBYID, 
            CHANGEDBYID, 
            DATEADDED, 
            DATECHANGED)
        select 
            @ADHOCQUERYID, 
            @IDSETREGISTERID, 
            SMARTQUERYINSTANCE.NAME, 
            'A dependent smart query', 
            'SMARTQUERYINSTANCE', 
            @RECORDTYPEIDSMARTQUERYINSTANCE, 
            SMARTQUERYINSTANCE.ID, 
            @CHANGEAGENTID, 
            @CHANGEAGENTID, 
            @CURRENTDATE, 
            @CURRENTDATE
        from dbo.SMARTQUERYINSTANCE
        where CAST(SMARTQUERYINSTANCE.DATAFORMITEMFILTERXML as nvarchar(max)) LIKE '%' + convert(nvarchar(36), @IDSETREGISTERID) + '%'; 

        --this handles dependencies to smartfields, which don't have a FK constraint


        --get the recordId for SmartField

        declare @RECORDTYPEIDSMARTFIELD uniqueidentifier;
        set @RECORDTYPEIDSMARTFIELD=(select ID from dbo.RECORDTYPE where BASETABLENAME='SMARTFIELD');

        insert into dbo.ADHOCQUERYDEPENDENCY 
            (ADHOCQUERYID, 
            IDSETREGISTERID, 
            TABLENAME, 
            TABLEDESCRIPTION, 
            DBTABLENAME, 
            RECORDTYPEID, 
            RECORDID, 
            ADDEDBYID, 
            CHANGEDBYID, 
            DATEADDED, 
            DATECHANGED)
        select 
            @ADHOCQUERYID, 
            @IDSETREGISTERID, 
            SMARTFIELD.NAME, 
            'A dependent smart field', 
            'SMARTFIELD', 
            @RECORDTYPEIDSMARTFIELD, 
            SMARTFIELD.ID, 
            @CHANGEAGENTID, 
            @CHANGEAGENTID, 
            @CURRENTDATE, 
            @CURRENTDATE
        from dbo.SMARTFIELD
        where CAST(SMARTFIELD.SMARTFIELDDATAFORMITEM as nvarchar(max)) LIKE '%' + convert(nvarchar(36), @IDSETREGISTERID) + '%';         

        -- This handles dependencies to tasks, which don't have a FK constraint


        -- Get the recordId for task

        declare @RECORDTYPEIDTASKCATALOG uniqueidentifier;
        set @RECORDTYPEIDTASKCATALOG=(select ID from dbo.RECORDTYPE where BASETABLENAME='TASKCATALOG');

        insert into dbo.ADHOCQUERYDEPENDENCY 
            (ADHOCQUERYID, 
            IDSETREGISTERID, 
            TABLENAME, 
            TABLEDESCRIPTION, 
            DBTABLENAME, 
            RECORDTYPEID, 
            RECORDID, 
            ADDEDBYID, 
            CHANGEDBYID, 
            DATEADDED, 
            DATECHANGED)
        select
            @ADHOCQUERYID, 
            @IDSETREGISTERID, 
            TASKCATALOG.NAME, 
            'A dependent task', 
            'TASKCATALOG', 
            @RECORDTYPEIDTASKCATALOG, 
            TASKCATALOG.ID, 
            @CHANGEAGENTID, 
            @CHANGEAGENTID, 
            @CURRENTDATE, 
            @CURRENTDATE
        from dbo.TASKCATALOG
        where CAST(TASKCATALOG.TASKSPECXML as nvarchar(max)) LIKE '%' + convert(nvarchar(36), @ADHOCQUERYID) + '%'; 
     end