USP_SMARTFIELD_VALIDATESMARTFIELDRENAME

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

  declare @INUSEMESSAGE nvarchar(350);
  declare @SOURCEQUERYVIEWCATALOGID uniqueidentifier;
  declare @QUERYNAME nvarchar(255);
  declare @OBJECTNAME nvarchar(128);

  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 name value.  The smart field is being used by the source analysis rule.';
        raiserror ('BBERR_DB_SMARTFIELDINUSE_SAR', 1, 111);
        raiserror (@INUSEMESSAGE, 16, 112);
        return 1;
      end
    end

    if  isnull(@OLDNAME, '') != ''
    begin
      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 name 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_EXPORTDEFINITION', 1, 111);
        raiserror (@INUSEMESSAGE, 16, 112);
        return 1;
      end

      select @SOURCEQUERYVIEWCATALOGID = [SOURCEQUERYVIEWCATALOGID] from dbo.[SMARTFIELD] where [ID] = @SMARTFIELDID;
      if @SOURCEQUERYVIEWCATALOGID is not null
      begin
          select @QUERYNAME = dbo.UFN_ADHOCQUERY_IDSMARTFIELDISINUSEBY(@OLDNAME + ' Smart Field');
          if isnull(@QUERYNAME, '') != ''
          begin
            set @INUSEMESSAGE = 'Cannot modify the name value.  The smart field cannot be renamed because the ''' + @QUERYNAME + ''' query uses one or more of the smart field values as a filter or output field.';
            raiserror ('BBERR_DB_SMARTFIELDINUSE_ADHOCQUERY', 1, 111);
            raiserror (@INUSEMESSAGE, 16, 112);
            return 1;
          end
      end
    end

        --Checking for smart fields used on warehouse queries

        declare @OLAPDATASOURCEID uniqueidentifier;
        declare @MARTKEY nvarchar(50);
        declare OLAPDATASOURCES cursor local fast_forward for
            select ID, MARTKEY
            from dbo.OLAPDATASOURCE

        open OLAPDATASOURCES
        fetch next from OLAPDATASOURCES into @OLAPDATASOURCEID, @MARTKEY;
        while(@@FETCH_STATUS = 0)
        begin
            declare @TABLENAME nvarchar(255);

            select @TABLENAME = tc.[TABLENAME]
            from dbo.SMARTFIELD s 
            inner join dbo.TABLECATALOG tc on s.TABLECATALOGID = tc.ID
            where s.ID = @SMARTFIELDID;

            select @QUERYNAME = dbo.UFN_ADHOCQUERY_FIELDISINUSEBY('v_QUERY_' + @MARTKEY + '_' + @TABLENAME,'')
            if isnull(@QUERYNAME, '') != ''
            begin
                set @INUSEMESSAGE = 'Cannot modify the name value.  The smart field cannot be renamed because the ''' + @QUERYNAME + ''' query uses one or more of the smart field values as a filter or output field.';
                raiserror ('BBERR_DB_SMARTFIELDINUSE_ADHOCQUERY', 1, 111);
                raiserror (@INUSEMESSAGE, 16, 112);
                return 1;
            end

            fetch next from OLAPDATASOURCES into @OLAPDATASOURCEID, @MARTKEY;
        end

        close OLAPDATASOURCES
        deallocate OLAPDATASOURCES

  end
  return 0;