USP_SMARTFIELD_VALIDATEVALUEGROUPCHANGE

CREATE procedure dbo.USP_SMARTFIELD_VALIDATEVALUEGROUPCHANGE
(
    @SMARTFIELDID uniqueidentifier,
    @OLDNAME nvarchar(125),
    @VALUEGROUPFIELDNAME nvarchar(255)
)
with execute as caller
as
  set nocount on;

  declare @INUSEMESSAGE nvarchar(350);
  declare @OBJECTNAME nvarchar(128);
  declare @EXPORTDEFINITION nvarchar(255);

  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 and [QUERYFIELD] = @VALUEGROUPFIELDNAME)
        begin
            set @INUSEMESSAGE = 'Cannot modify the ''Use value groups'' value.  The smart field is being used by the source analysis rule.';
            raiserror ('BBERR_DB_SMARTFIELDVALUEGROUPINUSE_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 @EXPORTDEFINITION = dbo.[UFN_EXPORTDEFINITION_SMARTFIELDVALUEGROUPISINUSEBY](@OBJECTNAME, @VALUEGROUPFIELDNAME);
      if isnull(@EXPORTDEFINITION, '') <> ''
      begin
        set @INUSEMESSAGE = 'Cannot modify ''Use value groups'' value.  The smart field cannot be changed because the ''' + @EXPORTDEFINITION + ''' export definition uses one or more of the smart field values as a sort or output field.';
        raiserror ('BBERR_DB_SMARTFIELDVALUEGROUPINUSE_EXPORTDEFINITION', 1, 111);
        raiserror (@INUSEMESSAGE, 16, 112);
        return 1;
      end
    end
  return 0;