USP_GENERALPURPOSEEMAIL_REMOVEFROMQUEUE

create procedure dbo.USP_GENERALPURPOSEEMAIL_REMOVEFROMQUEUE
(
  @QUEUEID uniqueidentifier = null
)
as
begin

  set nocount off;

  DECLARE @STATUSID as uniqueidentifier
  DECLARE @RETURNVALUE as integer
  SET @RETURNVALUE = 0

  set @STATUSID = (select top 1 ID from dbo.EMAILSTATUSGENERALPURPOSE where EMAILQUEUEGENERALPURPOSEID = @QUEUEID)

  begin try
    delete from dbo.EMAILQUEUEGENERALPURPOSE
        where ID = @QUEUEID and THREADID = '00000000-0000-0000-0000-000000000000'

    set @RETURNVALUE = @@rowcount
  end try
  begin catch
    exec dbo.USP_RAISE_ERROR
    return -2
  end catch

  -- returning a different value if item exists in queue, but is already in processing

  if (select top 1 1 from EMAILSTATUSGENERALPURPOSE where EMAILQUEUEGENERALPURPOSEID = @QUEUEID) = 1
    set @RETURNVALUE = -1

  begin try
    update EMAILSTATUSGENERALPURPOSE set STATUS = 101 where ID=@STATUSID;
  end try
  begin catch
    exec dbo.USP_RAISE_ERROR
    return -3
  end catch

  return @RETURNVALUE

end