USP_NAME_FORMATFUNCTIONDELETE

CREATE procedure dbo.[USP_NAME_FORMATFUNCTIONDELETE]
(
    @ID uniqueidentifier,
    @CHANGEAGENTID uniqueidentifier
)
with execute as owner
as 
begin
    begin try
        if @CHANGEAGENTID is null
            exec dbo.[USP_CHANGEAGENT_GETORCREATECHANGEAGENT] @CHANGEAGENTID output;

        declare @FUNCTIONNAME nvarchar(100) = '';
        declare @SQLTOEXEC nvarchar(500);

        /* Drop the function if it was user defined */
        /* Otherwise leave it because it could be used in other areas */
        select
            @FUNCTIONNAME = [FORMATSQLFUNCTION]
        from dbo.[NAMEFORMATFUNCTION]
        inner join dbo.[NAMEFORMATFUNCTION_USERDEFINED] on [NAMEFORMATFUNCTION].[ID] = [NAMEFORMATFUNCTION_USERDEFINED].[ID]
        where [NAMEFORMATFUNCTION].[ID] = @ID;

        if len(@FUNCTIONNAME) > 0
        begin
            declare @CONTEXTCACHE varbinary(128);

            --cache current context information

            set @CONTEXTCACHE = CONTEXT_INFO();

            --set CONTEXT_INFO to @CHANGEAGENTID

            set CONTEXT_INFO @CHANGEAGENTID;

            delete from dbo.[SQLFUNCTIONCATALOG] where [FUNCTIONNAME] = @FUNCTIONNAME;

            --reset CONTEXT_INFO to previous value

            if not @CONTEXTCACHE is null set CONTEXT_INFO @CONTEXTCACHE;

            set @SQLTOEXEC = 'if exists(select * from sys.objects where object_id = OBJECT_ID(N''[dbo].[' + @FUNCTIONNAME + ']'')) drop function dbo.[' + @FUNCTIONNAME + '];';
            exec sp_executesql @SQLTOEXEC;

            -- drop table value function

            set @SQLTOEXEC = 'if exists(select * from sys.objects where object_id = OBJECT_ID(N''[dbo].[' + @FUNCTIONNAME + '_TVF]'')) drop function dbo.[' + @FUNCTIONNAME + '_TVF];';
            exec sp_executesql @SQLTOEXEC;
        end

        -- use the system generated delete routine to allow proper recording of the deleting agent

        exec dbo.[USP_NAMEFORMATFUNCTION_DELETEBYID_WITHCHANGEAGENTID] @ID, @CHANGEAGENTID;
    end try

    begin catch
        exec dbo.[USP_RAISE_ERROR];
        return 1;
    end catch

    return 0;
end