USP_IDSETREGISTER_DELETEFORSMARTQUERYINSTANCE

CREATE procedure dbo.[USP_IDSETREGISTER_DELETEFORSMARTQUERYINSTANCE]
(
  @SMARTQUERYINSTANCEID 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.[IDSETREGISTERSMARTQUERYINSTANCE] on [IDSETREGISTER].[ID] = [IDSETREGISTERSMARTQUERYINSTANCE].[IDSETREGISTERID]
      where [IDSETREGISTERSMARTQUERYINSTANCE].[SMARTQUERYINSTANCEID] = @SMARTQUERYINSTANCEID;

    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 an 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