USP_IDSETREGISTER_CREATEORUPDATEFORIMPORTSELECTION

create procedure dbo.[USP_IDSETREGISTER_CREATEORUPDATEFORIMPORTSELECTION]
(
  @IMPORTSELECTIONID uniqueidentifier,
  @USEINQUERYDESIGNER bit = 0,
  @NUMROWS int = null,
  @CHANGEAGENTID uniqueidentifier = null,
  @IDSETREGISTERID uniqueidentifier = null output
)
as
  set nocount on;

  declare @ID uniqueidentifier;
  declare @CURRENTDATE datetime;

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

  set @CURRENTDATE = getdate();

  select
    @ID = [IDSETREGISTERID]
  from dbo.[IMPORTSELECTIONPROCESS]
  where [ID] = @IMPORTSELECTIONID;

  if @ID is null
    begin
      --Add new row to register...

      if @IDSETREGISTERID is null
        set @IDSETREGISTERID = newid();

      insert into dbo.[IDSETREGISTER] ([ID], [NAME], [DESCRIPTION], [RECORDTYPEID], [DBOBJECTNAME], [OBJECTTYPE], [STATIC], [NUMROWS], [USEINQUERYDESIGNER], [SITEID], [OWNERID], [ADDEDBYID], [CHANGEDBYID], [DATEADDED], [DATECHANGED])
        select
          @IDSETREGISTERID,
          [NAME] + ' (Imported)',
          [DESCRIPTION],
          [RECORDTYPEID],
          dbo.[UFN_IMPORTSELECTION_MAKETABLENAME](@IMPORTSELECTIONID),
          0,  --view or table

          1,  --static

          @NUMROWS,
          (case when @USEINQUERYDESIGNER is null then [USEINQUERYDESIGNER] else @USEINQUERYDESIGNER end),
          [SITEID],
          [OWNERID],
          @CHANGEAGENTID,
          @CHANGEAGENTID,
          @CURRENTDATE,
          @CURRENTDATE
        from dbo.[IMPORTSELECTIONPROCESS]
        where [ID] = @IMPORTSELECTIONID;

      update dbo.[IMPORTSELECTIONPROCESS] set
        [IDSETREGISTERID] = @IDSETREGISTERID,
        [CHANGEDBYID] = @CHANGEAGENTID,
        [DATECHANGED] = @CURRENTDATE
      where [ID] = @IMPORTSELECTIONID;
    end
  else
    begin
      --Update existing register info...

      set @IDSETREGISTERID = @ID;

      if isnull(@USEINQUERYDESIGNER, 0) = 0 and exists(select [ID] from dbo.[ADHOCQUERY] where dbo.[UFN_ADHOCQUERY_IDSETINUSE](@IDSETREGISTERID, [QUERYDEFINITIONXML]) = 1)
        begin
          raiserror('BBERR_IDSET_INUSEBYADHOCQUERY', 14, 44);
          raiserror('The ''Show this Selection in the Query Designer'' option cannot be set to false because the selection is in use by an ad-hoc query.', 16, 1);
          return 1;
        end

      update dbo.[IDSETREGISTER] set
        [NAME] = [IMPORTSELECTIONPROCESS].[NAME] + ' (Imported)',
        [DESCRIPTION] = [IMPORTSELECTIONPROCESS].[DESCRIPTION],
        [NUMROWS] = (case when @NUMROWS is null then [IDSETREGISTER].[NUMROWS] else @NUMROWS end),
        [USEINQUERYDESIGNER] = (case when @USEINQUERYDESIGNER is null then [IMPORTSELECTIONPROCESS].[USEINQUERYDESIGNER] else @USEINQUERYDESIGNER end),
        [SITEID] = [IMPORTSELECTIONPROCESS].[SITEID],
        [OWNERID] = [IMPORTSELECTIONPROCESS].[OWNERID],
        [CHANGEDBYID] = @CHANGEAGENTID,
        [DATECHANGED] = @CURRENTDATE
      from dbo.[IMPORTSELECTIONPROCESS]
      where [IDSETREGISTER].[ID] = @ID
      and [IMPORTSELECTIONPROCESS].[ID] = @IMPORTSELECTIONID;
    end

  return 0;