USP_SMARTFIELD_VALIDATESMARTFIELDDELETE

CREATE procedure dbo.USP_SMARTFIELD_VALIDATESMARTFIELDDELETE
(
    @SMARTFIELDID uniqueidentifier
)
with execute as caller
as        
    declare @TABLENAME nvarchar(128);
    declare @QUERYNAME nvarchar(255);
    declare @EXPORTDEFINITIONNAME nvarchar(255);
    declare @INUSEMESSAGE nvarchar(350);

    if @SMARTFIELDID is not null begin
        select 
            @TABLENAME = TABLECATALOG.TABLENAME 
        from 
            DBO.TABLECATALOG                         
            inner join dbo.SMARTFIELD on SMARTFIELD.TABLECATALOGID = TABLECATALOG.ID
        where
            SMARTFIELD.ID = @SMARTFIELDID;

        select @QUERYNAME = dbo.UFN_ADHOCQUERY_FIELDISINUSEBY('V_QUERY_' + @TABLENAME,'');

        if @QUERYNAME <> '' begin
            set @INUSEMESSAGE = 'The smart field cannot be deleted because it is being used by the ''' + @QUERYNAME + ''' query.'
            raiserror(@INUSEMESSAGE,13,1);
            return 1;
        end

        select @EXPORTDEFINITIONNAME = dbo.UFN_EXPORTDEFINITION_FIELDISINUSEBY('V_QUERY_' + @TABLENAME);

        if @EXPORTDEFINITIONNAME <> '' begin
            set @INUSEMESSAGE = 'The smart field cannot be deleted because it is being used by the ''' + @EXPORTDEFINITIONNAME + ''' export definition.'
            raiserror(@INUSEMESSAGE,13,1);
            return 1;
        end

        if OBJECT_ID('dbo.USP_GIVINGLEVELPROGRAM_VALIDATESMARTFIELDDELETE', 'P') is not null
            begin
            exec dbo.USP_GIVINGLEVELPROGRAM_VALIDATESMARTFIELDDELETE @SMARTFIELDID
            end

        if OBJECT_ID('dbo.USP_SOURCEANALYSISRULE_VALIDATESMARTFIELDDELETE', 'P') is not null
        begin
            exec dbo.[USP_SOURCEANALYSISRULE_VALIDATESMARTFIELDDELETE] @SMARTFIELDID
        end

        if OBJECT_ID('dbo.USP_MKTASKLADDER_VALIDATESMARTFIELDDELETE', 'P') is not null
        begin
            exec dbo.[USP_MKTASKLADDER_VALIDATESMARTFIELDDELETE] @SMARTFIELDID
        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
            select @QUERYNAME = dbo.UFN_ADHOCQUERY_FIELDISINUSEBY('v_QUERY_' + @MARTKEY + '_' + @TABLENAME,'')
            if isnull(@QUERYNAME, '') != ''
            begin
                set @INUSEMESSAGE = 'The smart field cannot be deleted because it is being used by the ''' + @QUERYNAME + ''' query.'
                raiserror (@INUSEMESSAGE, 13, 1);
                return 1;
            end

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

        close OLAPDATASOURCES
        deallocate OLAPDATASOURCES

end