USP_IDSETREGISTER_DELETEFORADHOCQUERY

CREATE procedure dbo.[USP_IDSETREGISTER_DELETEFORADHOCQUERY]
(
  @ADHOCQUERYID uniqueidentifier,
  @CHANGEAGENTID uniqueidentifier = null
)
with execute as caller
as
  begin
    set nocount on;

    declare @ID uniqueidentifier;
    declare @RESULT integer;

    select
      @ID = dbo.[IDSETREGISTER].[ID]
    from dbo.[IDSETREGISTER]
    inner join dbo.[IDSETREGISTERADHOCQUERY] on [IDSETREGISTER].[ID] = [IDSETREGISTERADHOCQUERY].[IDSETREGISTERID]
    where [IDSETREGISTERADHOCQUERY].[ADHOCQUERYID] = @ADHOCQUERYID;

    if @ID is not null
      begin
        if exists(select [ID] from dbo.[ADHOCQUERY] where dbo.[UFN_ADHOCQUERY_IDSETINUSE](@ID, [QUERYDEFINITIONXML]) = 1)
          begin
            raiserror('BBERR_IDSET_INUSEBYADHOCQUERY', 4, 44)
            raiserror('ID set cannot be deleted because it is in use by another ad-hoc query.', 16, 1)
            return 1;
          end

        if exists(select [ID] from dbo.[EXPORTDEFINITION] where dbo.[UFN_EXPORTDEFINITION_IDSETINUSE](@ID, [EXPORTDEFINITIONXML]) = 1)
          begin
            raiserror('BBERR_IDSET_INUSEBYEXPORTDEFINITION', 4, 44)
            raiserror('ID set cannot be deleted because it is in use by an export definition.', 16, 1)
            return 1;
          end
      end

    exec @RESULT = dbo.[USP_IDSETREGISTER_DELETEBYID_WITHCHANGEAGENTID] @ID, @CHANGEAGENTID;

    if @RESULT <> 0
      return @RESULT;

    return 0;
  end