USP_GENERALPURPOSE_EMAILJOB_DELETE

          CREATE procedure dbo.USP_GENERALPURPOSE_EMAILJOB_DELETE
          (
              @ID uniqueidentifier,
              @CHANGEAGENTID uniqueidentifier
          )
          with execute as owner
          as
          begin
            set nocount on;

            declare @contextCache varbinary(128);

            /* cache current context information */
              set @contextCache = CONTEXT_INFO();

            /* set CONTEXT_INFO to @CHANGEAGENTID */
              if not @CHANGEAGENTID is null
                  set CONTEXT_INFO @CHANGEAGENTID;

            -- this needs to be wrapped in a transaction to prevent competing updates

            begin tran DELETEJOB
            begin try
              declare @QUEUEID uniqueidentifier;
              set @QUEUEID = (select EMAILQUEUEGENERALPURPOSEID from dbo.EMAILSTATUSGENERALPURPOSE S where S.ID = @ID);

              delete dbo.EMAILQUEUEGENERALPURPOSE where ID = @QUEUEID;

              update dbo.EMAILSTATUSGENERALPURPOSE set STATUS = 18 where ID = @ID;

              -- attempt to drop email processing table for this job if it exists

               if @QUEUEID is not null
                   begin
                    -- we now need to cleanup the merge table

                    declare @MERGESQL as nvarchar(255)
                    declare @MERGETABLENAME as nvarchar(305)

                    set @MERGETABLENAME = dbo.UFN_GENERALPURPOSEEMAIL_GETDATATABLENAME(@QUEUEID)
                    set @MERGESQL = 'if object_id(''' + @MERGETABLENAME + ''') is not null drop table ' + @MERGETABLENAME + ';'
                    exec sp_executesql @MERGESQL;

                    declare @MERGEDATASETTABLE as nvarchar(128)
                    set @MERGEDATASETTABLE = dbo.UFN_GENERALPURPOSEEMAIL_GETMERGEDATASETTABLENAME(@QUEUEID)
                    set @MERGESQL = 'if object_id(''' + @MERGEDATASETTABLE + ''') is not null drop table ' + @MERGEDATASETTABLE + ';'
                    exec sp_executesql @MERGESQL;
                   end

            end try
            begin catch
              if @@trancount > 0
                rollback tran DELETEJOB
                  exec dbo.USP_RAISE_ERROR
              return 0
              end catch
            if @@trancount > 0
                commit tran DELETEJOB        

            /* reset CONTEXT_INFO to previous value */
              if not @contextCache is null
                  set CONTEXT_INFO @contextCache;         

              return 0;

          end