USP_EMAILJOB_UPDATECOUNTS

CREATE procedure dbo.USP_EMAILJOB_UPDATECOUNTS(@ID int)
as
begin

    declare @EMAILID int
    select @EMAILID = EMAILID from dbo.EmailJob where ID = @ID

    declare @NUMREQ int
    declare @NUMOPTEDOUT int
    declare @NUMEMPTYADDRESS int
    declare @NUMINVALIDACCOUNT int
    declare @NUMINACTIVEACCOUNT int

    select @NUMREQ = COUNT(1) from dbo.Email_Recipient E where E.EmailID = @EMAILID
    select @NUMOPTEDOUT = COUNT(1) from dbo.Email_Recipient E where E.EmailID = @EMAILID and E.OptOut = 1
    select @NUMEMPTYADDRESS = COUNT(1) from dbo.Email_Recipient E where E.EmailID = @EMAILID and E.InvalidAddress = 1
    select @NUMINVALIDACCOUNT = COUNT(1) from dbo.Email_Recipient E where E.EmailID = @EMAILID and E.InvalidAccount = 1
    select @NUMINACTIVEACCOUNT = COUNT(1) from dbo.Email_Recipient E where E.EmailID = @EMAILID and isnull(E.INACTIVE,0) = 1 and E.OptOut <> 1 and E.InvalidAccount <> 1 and E.InvalidAddress <> 1
    select @NUMREQ, @NUMOPTEDOUT, @NUMEMPTYADDRESS, @NUMINVALIDACCOUNT, @NUMINACTIVEACCOUNT

    update dbo.EmailJob set
        NumberRequested = @NUMREQ,
        NumberAbsentAddress = @NUMEMPTYADDRESS,
        NumberInvalidAccount = @NUMINVALIDACCOUNT,
        NumberOptedOut = @NUMOPTEDOUT,
        NumberInactiveAccount = @NUMINACTIVEACCOUNT
    where ID = @ID

end