USP_SMARTFIELD_VALIDATESOURCEQUERYVIEWCHANGE

CREATE procedure dbo.USP_SMARTFIELD_VALIDATESOURCEQUERYVIEWCHANGE
(
  @SMARTFIELDID uniqueidentifier
)
with execute as caller
as
    set nocount on;

  declare @INUSEMESSAGE nvarchar(350);
  declare @QUERYNAME nvarchar(255);
  declare @OBJECTNAME nvarchar(128);
  declare @NAME nvarchar(100);

  if @SMARTFIELDID is not null
  begin

    if object_id('dbo.MKTSOURCEANALYSISRULEFIELDS', 'U') is not null
    begin
      if exists(select 1 from dbo.[MKTSOURCEANALYSISRULEFIELDS] where [SMARTFIELDID] = @SMARTFIELDID)
      begin
        set @INUSEMESSAGE = 'Cannot modify the source view value.  The smart field is being used by the source analysis rule.';
        raiserror ('BBERR_DB_SMARTFIELDINUSE_SOURCEQUERYVIEW_SAR', 1, 111);
        raiserror (@INUSEMESSAGE, 16, 112);
        return 1;
      end
    end

    select @OBJECTNAME = replace(upper('V_QUERY_SMARTFIELD' + convert(nvarchar(36), [TABLECATALOGID])), '-', '') from dbo.[SMARTFIELD] where [ID] = @SMARTFIELDID;
    select @QUERYNAME = dbo.[UFN_EXPORTDEFINITION_SMARTFIELDISINUSEBY](@OBJECTNAME);
    if isnull(@QUERYNAME, '') <> ''
    begin
      set @INUSEMESSAGE = 'Cannot modify the source view value.  The smart field cannot be changed because the ''' + @QUERYNAME + ''' export definition uses one or more of the smart field values as a sort or output field.';
      raiserror ('BBERR_DB_SMARTFIELDINUSE_SOURCEQUERYVIEW_EXPORTDEFINITION', 1, 111);
      raiserror (@INUSEMESSAGE, 16, 112);
      return 1;
    end

    select @NAME = [NAME] from dbo.[SMARTFIELD] where [ID] = @SMARTFIELDID;
    select @QUERYNAME = dbo.[UFN_ADHOCQUERY_IDSMARTFIELDISINUSEBY](@NAME + ' Smart Field');
    if isnull(@QUERYNAME, '') <> ''
    begin
      set @INUSEMESSAGE = 'Cannot modify the source view value.  The smart field cannot be changed because the ''' + @QUERYNAME + ''' query uses one or more of the smart field values as a filter or output field.';
      raiserror ('BBERR_DB_SMARTFIELDINUSE_SOURCEQUERYVIEW_ADHOCQUERY', 1, 111);
      raiserror (@INUSEMESSAGE, 16, 112);
      return 1;
    end
  end

  return 0;